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

[sxiong1]

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. 

1000	1002	1007	1008	7000	7002	9002	9000
0	3	3	19	19	27	27	29

[BS_THE_ANALYST]

@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

[BS_THE_ANALYST]

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

[sxiong1]

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