Expand my Community achievements bar.

SOLVED

(Classic) Transform Key value pairs into separate columns

Avatar

Level 2

Hi,

I have a question regarding changing typical JSON key-value pairs into DB columns.

Here's the data schema I'm currently trying to transform:

user channel optstatus

1Emailtrue
1SMSfalse
2Emailtrue
2SMStrue
3Emailfalse
3SMStrue

 

I want to take the value pairs from "channel" and "optstatus" and create a new column in the data schema that would be fed with its values, something like this:

user channel optstatus Email SMS

1Emailtruetruefalse
1SMSfalsetruefalse
2Emailtruetruetrue
2SMStruetruetrue
3Emailfalsefalsetrue
3SMStruefalsetrue

 

Or to simplify something like that would be also good:

user Email SMS

1truefalse
2truetrue
3falsetrue

 

I don't wanna change the already-existing structure of the data schema apart from adding new columns. Would I do it in a workflow, query and enrichment, or do some SQL filtering? I would really appreciate your help. Cheers!

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Hello @nought 
Please try below approach:
-> [split] Split by channel

-> [enrichment] set @emailOPT with optstatus (the inbound transition value)

-> [enrichment] set @smsOPT with optstatus (the inbound transition value)
-> [union] chose selection of columns for reconciliation. set user as reconciliation key.

kishorep_0-1629389478692.png

kishorep_1-1629389629052.pngkishorep_2-1629389642671.pngkishorep_3-1629389660559.png


Hope this helps!

View solution in original post

4 Replies

Avatar

Community Advisor

Can you elaborate more? whats your end goal? also, is your source schema custom? or ootb?

Avatar

Level 2

My goal is to have columns with unique attributes and their values - currently, I'm getting key-value data through SOAP calls and that JSON-like structure is unnecessarily more complex to work with when it comes to applying rules, filters etc.

 

The data schema I'm referring to is custom.

Avatar

Correct answer by
Employee Advisor

Hello @nought 
Please try below approach:
-> [split] Split by channel

-> [enrichment] set @emailOPT with optstatus (the inbound transition value)

-> [enrichment] set @smsOPT with optstatus (the inbound transition value)
-> [union] chose selection of columns for reconciliation. set user as reconciliation key.

kishorep_0-1629389478692.png

kishorep_1-1629389629052.pngkishorep_2-1629389642671.pngkishorep_3-1629389660559.png


Hope this helps!

Avatar

Level 2

This is exactly what I was looking for! Thank you, kishorep, this is invaluable!