reshaping data

[Colin]

I have data that with columns: ID, Category, V1, V2, V3, ..., VN

N is unknown in advance - it is specific to the data.

Category has an unknown M number of levels - it is specific to the data.

I want to reshape that data to get columns: ID, Category1V1, Category1V2, ...., Category1VN, Category2V1, Category2V2, ....., CategoryMVN

If I knew N and M in advance then I could manually set up the work flow using filtering and joins, but I don't know N and M in advance. I need the work flow to react dynamically to the structure of the data.

I can do this using looping in R but I want to do it natively in Alteryx. Is there a way to do this?

Colin

[JordanB]

Hi @Colin

I had just a quick minute to take a look at this question so forgive me if it is not the full deal, but I think it should set you on your way. Please see the attached workflow (Built in 10.6). 

I used a combination of transpose, multi-row nd crosstab. 

Best,

Jordan Barker

Solutions Consultant 

[Colin]

Hi Jordan,

The transpose step doesn't return any rows when I use "Dynamic or Unknown Fields". It requires me to select V1 or V2 or V3 before it returns anything.

Colin

[JordanB]

Hi @Colin

When you insert the transpose tools it should by default select all fields as the data fields? 

Can you show me a screenshot?

Best,

Jordan Barker

[Colin]

Hi Jordan,

Here is an example showing how no data rows come through from the transpose, and also an R script showing what I want to happen.

Colin

[JordanB · Accepted solution]

Hi @Colin

Please find my attached workflow which will give you the same result as your R workflow. 

Best,

Jordan Barker

[Colin]

Thanks Jordan. That's working :-). Now I will go through it and figure out how it works...

Colin

[JordanB]

Okay sounds good @Colin! Please let me know if you have any questions.

Best,

Jordan Barker

Solutions Consultant