Transpose Row data to Columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
Solved! Go to Solution.
- Labels:
- Preparation
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would suggest the XML tool under the Parse category, or instead, reading in the data as XML instead of as a single cell.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I tried that first. It doesn't work with this type of data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
