Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

JSON Parse to table

JarkkoP
7 - Meteor

HI Community, 

 

Im struggling with a JSON converting problem that I cannot figure out. I have a JSON extract as below, that has exchange rates for specific dates and currencies. The data is structured so, that the Date and value is on top, and the currency information is below the actual data. the 0,1 ect defines the data groups. How can I convert this to the following format? All help is much appriciated! :=)

 

DateValueCurrencyCurrency denomCurrencyNameFiECBpublished  
13.01.20229,99333NOKEURNorjan kruunuTrue  
14.01.20229,986NOKEURNorjan kruunuTrue  
13.01.202210,238SEKEURRuotsin kruunuTrue  
etc...       

 

 

JarkkoP_1-1642414157719.png

 

9 REPLIES 9
atcodedog05
22 - Nova
22 - Nova

Hi @JarkkoP 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1642416001816.png

 

Hope this helps : )

 

JarkkoP
7 - Meteor

Thanks Atcodedog05!

 

I tested the workflow, but it only brings the first date of the dataset (13.1.2022) The dataset has multiple dates, in the example there is 14.01.2022, but once the workflow works, there will be hundreds).

 

Any ideas how to get the other datasets included also? 

JarkkoP_2-1642416334425.png

 

 

JarkkoP_1-1642416314862.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @JarkkoP 

 

Lets bring in all and later you can keep the required.

 

Workflow:

atcodedog05_0-1642416542236.png

 

Hope this helps : )

 

JarkkoP
7 - Meteor

Almost there :=) But the problem is where I ran into before, is how do I transpose the table so that the 2 different dates for the same currency go on their own rows and the currency and other lables are copied. as in the example above. 

 

JarkkoP_0-1642416702358.png

 

 

DateValueCurrencyCurrency denomCurrencyNameFiECBpublished  
13.01.20229,99333NOKEURNorjan kruunuTrue  
14.01.20229,986NOKEURNorjan kruunuTrue  
13.01.202210,238SEKEURRuotsin kruunuTrue  
etc...       

 

OllieClarke
15 - Aurora
15 - Aurora

Hi @JarkkoP 

 

Try this approach:

OllieClarke_0-1642417779465.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @JarkkoP 

 

My take on it. Should be dynamic too.

 

Workflow:

atcodedog05_0-1642418379998.png

 

Hope this helps : )

JarkkoP
7 - Meteor

 Big thanks OllieClarke & Atcodedog05! both of the solutions do the trick! :=)

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @JarkkoP 

 

Also, you are allowed to mark multiple posts as solutions so feel free to 🙂

Cheers and have a nice day!

JarkkoP
7 - Meteor

Thanks, I was struggling with which one to accept 😄 Both are not approved as solutions :=) 

Labels