Using single statement to create multiple fields

Question

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

YearMonthPart_No Revenue  Margin  Quantity 20191ABC   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 Answer

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