From Event to Profile - Data Distiller | Community
Skip to main content
Michael_Soprano
Michael_Soprano
Level 10
May 11, 2025
Solved

From Event to Profile - Data Distiller

  • May 11, 2025
  • 1 reply
  • 413 views

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

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by supratim320

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!

1 reply

S
supratim320Accepted solution
Level 5
May 12, 2025

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
    Michael_SopranoAuthor
    Level 10
    May 13, 2025

    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?

    S
    supratim320
    Level 5
    May 14, 2025

    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!

    Terms & ConditionsAccessibility statement

    Loading footer...