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 Values "key","value" as Columns

alextruesdale
5 - Atom

Hello,

 

I have a .csv with nested JSON rows that I would like to break into columns. I have been following steps from a similar posting from earlier this year, which have me close to my goal.

 

An example input row looks like the following (formatted here for legibility):

[{
     "key":"Pattern",
     "value":["Solid"]
},
{
     "key":"Worldstock Country",
     "value":["Peru"]
},
{
     "key":"Neck Style",
     "value":["Scoop Neck"]
}

... and it continues

 

My workflow gets me to this point so far:

 

RecordIDKeyValue
1  

Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,materialSolid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool
2Recommended Use,Primary Color,Battery Type,Heel HeightNOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\
3Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe WidthLow (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M)

 

 

This is close to what I want to achieve - something like this:

(important that the recordID be preserved)

 

RecordIDPatternWorldstock CountryNeck StyleSleeve-lengthFrontCare Instruction
1SolidPeruScoop NeckLong SleeveFlat FrontHand Wash

 

What is an efficient way to achieve this final outcome? Workflow attached.

Any and all help/suggestions greatly appreciated.

2 REPLIES 2
NickC
Alteryx Alumni (Retired)

Hello Alex,

 

The workflow you sent didn't include the text file you were working from, so I have built the solution from where you got to... 

 

RecordIDKeyValue
1  

Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,materialSolid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool
2Recommended Use,Primary Color,Battery Type,Heel HeightNOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\
3Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe Width

Low (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M)

 

 

 

 

The format you want the data in doesn't work particularly well because as you can see the 'key' changes for each record ID.  I have got it in the format you want but it still shows a lot of blanks.

 

 

JSON.png

 

 Hope this helps.

 

Thanks,

Nick

 

 

 

alextruesdale
5 - Atom

Hi Nick,

 

Thank you for the response and solution. I recently came to very similar conclusion (admittedly with a few extra steps).

There are quite a few empty spaces with the format that the data is in; for the time, that will be a separate issue -- happy now to have it in a more machine-readable format. 

 

I have posted my workflow below as a reference for others if they want a similar approach.

 

Many thanks (text file to be included next time),

Alex

Labels