community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

JSON Values "key","value" as Columns

Highlighted

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.

Alteryx
Alteryx

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

 

 

 

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