How to convert name value pair (non-JSON) within a table column?

Question

Looking for help to process the data with name value pair in the format of "name1!value1, name2!value2, name3!value3, .etc." inside table using Alteryx designer. The number of pairs within this column "PATH" varies in the data set. Would like to have the output in the format with namex as field name and valuex as value under the namex (similar to table below). Thanks.

100010021007100870007002900290000331919272729

Jan-01-06-ID_Path-10Rec.csv

sxiong1 · Answer

Yes, need to keep the ID field. Thanks, your suggestion worked.

BS_THE_ANALYST · Answer

Another remark I'll add. You didn't mention it - but you may want to keep the ID information.

All you need to do is add an extra step on your crosstab:

All the best,
BS

BS_THE_ANALYST · Answer

@sxiong1 here's what I'd recommend:

Step 1: Load the data in using a File Input tool

Step 2: Split the field 'Path' to rows on the delimiter ',' using a Text to Columns tool:

Step 3: Split the field 'Path' to columns on the delimiter '!' using a Text to Columns tool:

Step 4: Cross tab your data (pivot rows into columns):

Note, you may saee some fields with multiple values in. This is because that you had key values that were the same.

Hopefully that helps, or at least gets you started 😎.

Any questions, let me know.

All the best,
BS

KeyValuePivoting.yxzp