Suggestions requested on how best to restructure data

Question

I have an Excel dataset laid out in the structure attached. I need to be able to analyse this data over time but I am a bit stuck as to how to best transform the data.

I want to reconfigure the data structure so that both the 'quantity' and '$ value' fields shown in the example attached have a date linked to them. After loading this example data into Alteryx, the date is sat above the 'quantity' field, with the field above '$ value' being null due to the data being merged in Excel. I have tried transposing then using a multi row formula to populate the null cells with the date from the row above (e.g. WK16 FY19) but when I go to cross-tab the resulting data to put the dates back as columns, it doesn't yield the result I'm looking for.

sample data.xlsx

ccanterman · Answer

And then the problem with my original solution is that I had the Group By in the top Multi-Tool set to only Category. Which works great if the Category is only listed once, but falls apart when you had a Category listed more than once. The fix for that is to modify the Group By to have Category, Item, and ID checked (or whichever represents a unique identifier - maybe ID?)

But CoG's solution is way better and I'd use that over mine!

ccanterman · Answer

@figures500 - I think the problem you're running into is caused by the RegEx_Replace.

As written, it's taking a single digit for the Sub ID column

Note - in the screenshot, I disabled the 3rd formula so you can see how the SubID column looks after the 1st formula tool is applied:

I changed it to add a "\D" to mean "not a digit". I'm sure @CoG can do it more elegantly but hey, mine works :)

This way, your SubID column will be the full number from the "Name" column, not just the last digit

That way, when you turn back on the Formula #3 and run everything through, you get a full Join with no lost Left outputs

figures500 · Answer

Thanks for your help! Similar to the original solution, your example works perfectly but when I try and apply it to the larger dataset (sample attached), a lot of records are being dropped at the left output of the final join. I have tried tweaking your workflow to no avail so any help you can give me to show where I'm going wrong would be greatly appreciated!!

larger anonymised sample.xlsx