From Event to Profile - Data Distiller

[Michael_Soprano]

I have got such a code:

However its shows errors like that:

• 6:13:11 PM > ErrorCode: 42601 queryId: 39d83348-6ae0-4f92-8d7e-8c79b703c018 Syntax error encountered. Reason: [line 2:18: mismatched input '.' expecting {',', ')'}]

That part works perfectly:

select
_pwcglobptrsd.identification.core.email,
productListItems.name
from
demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.purchases'

Both tables are enabled to profile. 

Enable which I would like insert INTO:

- _pwcglobptrsd.email STRING

- _pwcglobptrsd.Products ARRAY OF STRING

[supratim320 · Accepted solution]

Hi @Michael_Soprano ,

considering all the field types (target & source) are correct, please try with below way. Using "STRUCT" keyword you have to create the json like structure (unlike to sql where you use fields to be insert in insert clause): Learn More 

Sample:

INSERT INTO from_event_to_profile_dataset



With demo_table as (select
_pwcglobptrsd.identification.core.email as emailAddress,
productListItems.name as productListNames
from
demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.purchases')



SELECT 

STRUCT (emailAddress AS `email`, productListNames AS Products) AS _pwcglobptrsd

FROM demo_table

Hope this helps!

[Michael_Soprano]

Thanks, it helped. However I have got another problem:

Source table: productListItems.name is a string

Destination table: _pwcglobptrsd.productListNames is an array 

If in the array for the given email is already exists then I would like to append new record for the end of this array. 

Do you have any idea how to sort that out?

[supratim320]

Hi @Michael_Soprano,

You can perform an outer join between the event dataset (current array) and resulting profile dataset (existing array) based on common id and then perform an array_join (considering both event and profile elements are array): resource

P.S.

1. I have never done it, so you need to do a through test

2. assuming your target dataset in XDM profile class, it should hold on to latest record only - so please consider this into your usecase

Please let me know if it works!