Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

(Classic) Transform Key value pairs into separate columns

nought
Level 2
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
kishorep
Correct answer by
Employee
Employee

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

1 Reply
David__Garcia
Level 9
Level 9

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

nought
Level 2
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.

kishorep
Correct answer by
Employee
Employee

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

nought
Level 2
Level 2

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