Need help in Data Preparation

[stj1120]

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.

[neilgallen · Accepted solution]

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.

[stj1120]

Hi @neilgallen 

Thank you for the solution. That worked amazingly! 

I need one more help.

1) How can I get the number to be included in the first row as 254863 / Badam Oil 1 L and also

2) place that number in a separate column called Item. 

Awaiting your response.

Thank you again!

Br,

[stj1120]

In this scenario how do we align the Item number to the product name as "Item / Product Name" dynamically. I tried in Multi Row-Formula tool but getting stuck. Kindly help. Many thanks!

[neilgallen · Accepted solution]

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.

[stj1120]

@neilgallen you are amazing!

thank you for your wonderful support.