How to use cross tab to transform data

Question

Hi team,

I have this set of data below:

The desired format is this:

I have using transpose and formula tool to try to manipulate the data. I think the next step is to use cross-tab, but I am not able to get the desired output format. Please suggest anything if you can think of a smarter solution than my current workflow. Thanks!

Screenshot of transpose tool:

Screenshot of formula tool:

New Workflow4.yxmd

danilang · Answer

Hi @wuaw

Here you go

Once your data is transposed, use a Regex Parse tool to separate the column header from the type.  You can also use a Formula tool with substring/findstring combinations instead of the regex.  After this you can use a Cross Tab.  This changes the output column order and replaces spaces in column names with underscores as shown in the top container.

To keep the underscores and column order add a Multirow tool to build the unique column numbers.  Cross Tab with the column numbers as headers, since the new columns get placed in numerical order.  Use a Dynamic Rename to change the numbered columns to the original column names.  the bottom Summarize build a list of unique column names and numbers that the Dynamic Rename uses to perform the substitution.

Dan

CroosTabOnIDAndType.yxmd

Qiu · Answer

@wuaw 
I hope this works for you.

New Workflow4.yxmd