I need assistance pivoting this data, please

Question

I attached a photo here. The top table is my input into Alteryx. This is exactly how it will come in every time, so same cell spacing as shown in picture. Key note here is this needs to be dynamic because in the future, there could be way more than 5 data columns (Customer No and Customer Name will always be consistent). So in future this could go to F50. And the tag/item/customer # will be different every time. The tag, item and customer # will ALWAYS be in that order (so tag will be row 1, row 2 will be item, and row 3 is customer # in every file). The column headers are F5 - FXamount

The second table is something I manually created to show what I need the output to be. But I cannot figure out how to achieve this. I tried transposing and using formulas, etc and it simply will not work. It's tricky because the tag, item, and customer # are on 3 separate rows.

Any help here would be so greatly appreciated! Thank you :)

AncientPandaman · Answer

i built one macro to concat the header. concat multi-row and promote to header - Alteryx Community

the rest may just transpose + text to column

KGT · Answer

You have different treatment for the first 3 rows to the rest (doesn't matter how many other rows), so split the data in 2 using a recordID and then filter on RecordID<=3. You now have 2 streams.

You will need a lookup (or replace buried in a formula, but a lookup is neater) for what the first 3 rows mean.

Line/RecordIDHeader1Tag2Item3CustomerNo

On the first 3 records:

* Transpose with only recordID as Key, // This will always get additional columns as long as "Dynamic or unknown" is ticked in the data fields
* Filter for !IsNull([Value]) // This will get rid of all Null Values
* Join the header from the lookup table.
* Cross-tab with header as the header and Name as the Group

On the other records:

* Transpose with Customer No & Customer Name as Key. (Discard RecordID if you like)
* Join to top dataset based on [Name]=[Name]
* Re-arrange