Using single statement to create multiple fields

[dralban]

I have a file that for each Year and Month and part number has three values - Revenue, Margin, and Quantity.

Year	Month	Part_No	Revenue	Margin	Quantity
2019	1	ABC	9,027,305	1,235,421	9,704

I need to take this and transform it into a list that shows, for each part number, 72 properly named columns. Here is an example:

PART_NO, PYJANR, PYJANM, PYJANQ, PYFEBR, ...

PYJANR means Prior Year, January, Revenue. I need to create the same for each month of prior year, current year, and all for revenue, margin and quantity.

It is currently in SAS in a large IF statement that creates multiple fields and assigns them values. Is there a way for me to do something similar in Alteryx, or do I need to create 72 separate variables in Formula tool.

[AngelosPachis · Accepted solution]

Hi @dralban ,

I've created a workflow that answers your question. Essentially it's just a matter of cross tabing / transposing your data and the rest of the workflow (inside the comment box) is just to generate the data to work with.

So for each Part No, you would now have 73 fields (3 categories x 12 months x PY or CY = 72 + Part No = 73).

Hope that helps.

Regards,

Angelos

[dralban]

Thank you, this is perfect!