Need help in Data Preparation

Dear all,

I need your help in sorting out this data preparation. I tried many times, but getting stuck at Transpose.

The data in Transpose is not aligned properly.

Apologies, I don't have the proper Alteryx output of this excel data because, this is the sample data that I downloaded from the external website and finally I wanted to build Tableau dashboard using this Alteryx output based on Affliate, Months On Hand (MoH), and Month-Year.

Kindly help me out with the complete workflow as I don't understand how this data can be properly alligned.

A Quck help can be really appreciated!

Thank you.

Best Regards,

Teja.

Sample.xlsx

Workflow

Preparation

Accepted answers

neilgallen

hello @stj1120

Attached is what I think you're looking for, based on the description. You could replace the text input with your excel file and you should be fine.

This is one of those scenarios where you end up pivoting the data twice to get the end result.

DataPrep.yxmd

neilgallen

with no respect to the other workflow, this is how you could use the multi-row formula tool to account for this:

if regex_match([Row+1:Field1],"\d+") then [Row+1:Field1]+ "\" + [Field1] 

elseif regex_match([Field1],"\d+") then [Row-1:Field1] 

elseif isnull([Field1]) then [Row-1:Field1] 

else [Field1] 

endif

this checks the row below for a number only string, and then combines this with the current field. if the field is the aforementioned number only, it pulls from the row above. if the field is null, it pulls from the above row. Otherwise it will leave the field as is.