Transpose Row data to Columns

[The_Rad_Valentina]

Hello Alteryx Gurus, 

I am try to transpose a single cell data into columns with the name of column and value for the columns being in the same single cell. Example of data below. Data can be a lot longer or shorter depending on a record. 

{"eventId":"7ef3d089-05652-1eda-b8aa-6e2d2b5983456","payLoad":"{\"conditionTable\":\"73\",\"application\":\"V\",\"salesOrganization\":\"831\",\"distributionChannel\":\"06\",\"materialNumber\":\"000006859276420158\",\"salesUnit\":\"EA\",\"priceRequired\":\"N\",\"pricing\":[{\"conditionRecordNumber\":\"0006171071\",\"conditionType\":\"VKP0\",\"validFromDate\":\"20200818\",\"validToDate\":\"99991231\",\"pricingItem\":{\"rate\":0,\"rateUnit\":\"USD\",\"conditionPricingUnit\":\"1\",\"conditionUnit\":\"EA\"}},{\"conditionRecordNumber\":\"0006170985\",\"conditionType\":\"ZKP0\",\"validFromDate\":\"20200818\",\"validToDate\":\"99991231\",\"pricingItem\":{\"rate\":449.99,\"rateUnit\":\"USD\",\"conditionPricingUnit\":\"1\",\"conditionUnit\":\"EA\"}},{\"conditionRecordNumber\":\"0006170999\",\"conditionType\":\"ZSRP\",\"validFromDate\":\"20200818\",\"validToDate\":\"99991231\",\"pricingItem\":{\"rate\":449.99,\"rateUnit\":\"USD\",\"conditionPricingUnit\":\"1\",\"conditionUnit\":\"EA\"}}]}"}

Results I am lookin for:

Name	Value
eventId	7ef3d089-05652-1eda-b8aa-6e2d2b5983456
conditionTable	73
application	V
salesOrganization	831
distributionChannel	06
materialNumber	000006859276420158

and so on...

Hoping for some help 🙂

[mbarone]

I would suggest the XML tool under the Parse category, or instead, reading in the data as XML instead of as a single cell.

[The_Rad_Valentina]

I tried that first. It doesn't work with this type of data. 

[echuong1 · Accepted solution]

There are more elegant ways of doing this, but the easiest way is to use text to columns to split the values into separate rows, and then again to split the column into the name and value pieces. Some of the items have overall categories in them (like condition table also contains payload) and would produce 3 columns. You can use a formula to identify the correct pieces.

[mbarone]

I'm not an XML expert, but that certainly appears to be XML.  Maybe the quotes or brackets are not standard - not sure.  Shy of using the XML tool/input, I'd probably split to rows on the backshalsh and parse it out from there using a series of regex formulas and/or transpose/crosstab or even a multi-row tool.

[The_Rad_Valentina]

Thank you! Almost there..

I'll just have to probably add a formula tool instead of the cleanse tool because now my rates, which should be a $$ doesn't have a decimal point....also seems like my data has several records per material number. Split to rows?