Suggestions requested on how best to restructure data

[figures500]

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. 

[Carolyn]

You might want to check out the Daily Challenge #439 - it's basically this exact question

See attached for one way that you could do this

I modified your Input file to have some actual values in it as well as adding a 2nd row for product DEF. 

I also set the Input Tool to have "Row 1 contains data" checked, since I find it easier to work with in situations like this. 

From there, I used the Arrange Tool which is what will let you create 2 columns, one for Quantity and one for $ Value. 

Then the bottom path is where I created a column with the date values, which was then Joined with the top path. 

[figures500]

Thank you so much for your help!

The actual dataset I'm working with is much longer (e.g. ~500 category-item combinations) and broader (~200 time periods) than the sample I shared. So when I try to follow the approach you have shared, there are a lot of records that don't join because the 'record ID' on the top branch is far higher than the 'record ID' on the bottom date branch.   

[CoG]

I updated @Carolyn 's workflow to be more dynamic to account for this added requirement (increased column number):

Notice that we have replaced the Arrange Tool with Transpose and Cross Tab Tools, while adding some Formula Tool magic to keep track of ID's. Hope this helps and Happy Solving!

[figures500]

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!!

[Carolyn]

@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

[Carolyn]

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!