Challenge #49: Reformatting Transactional Data

[izamryan]

A few curveballs, this took me ~45 minutes from start to end, and my solution still retains the same data quality issue as in the original, so my result differs from the suggested output.

By the way! This makes it challenge #16 that I've completed. @Community_Admin  Where do I go to get my new Camp 1 badge!

Spoiler

This took 3 steps: 

1. Create the main body of the Report
The curveball in here was the dates - my first iteration didn't work properly because there's a difference between yyyy-mm-dd and yyyy-MM-dd

2. Hierarchy of Market/Product/Type
There's another curveball here in that I originally set the hierarchy to Product/Market/Type before realising that actually, Market/Product/Type is in alphabetical order! I would've been tempted to keep the hierarchy the same as in the data input without re-shaping it here.

3. Glue together main body with the headers
Dynamic Rename is your friend.


Some thoughts

I keep the "dirty" data because, in this workflow it should be robust enough to not require "hard-coding" for the edge cases. In theory, because we exploit the "relative position" of the columns in the data, when this workflow is presented with more of the similar data but with more Market/Product/Type variations, it should be able to handle it gracefully.

Handle it gracefully because I've added "Dynamic or Unknown Columns" to the configuration, so in theory if the data set adds more columns, it "Should Just Work TM".

It would break down if the hierarchy was increased (i.e. more than 3- levels of Market/Product/Type to be a 4th level of Market/Product/Type/Variant) but that's easily fixable by changing the filters from Skip 1st 3 to Skip 1st 4 etc. I could probably do that with an Input Form but that's being fancy for now.

[TwylaXBlack]

Spoiler

Spoiler

Solved

[MarielJinang14]

Spoiler

[mhou]

🙂

[mulvihi11]

Transpose tool is always a trial and error experience for me. Happy to have the additional practice!

Spoiler

[htrivedi123]

My solution to this challenge, I found it rather simple than other challenges or am I getting better :)...GO Alteryx

[kennetheng]

Pretty cool challenge! 

[Rohit_G13]

This was a fun challenge which needed a bit of thinking even though the output seemed straightforward

Regards

Rohit Govind

[jacob_kahn]

This was much more simple than I thought it was going to be.

Thank you, Alteryx! 🙂 

J

Spoiler

[TSA05]

I used a slightly different (possibly inefficient) approach that clicked in the first go.

1. Use CrossTab + Transpose to get all headers and dates into the desired format

2. Use Union to stack all of the above formats together

3. Data Cleansing to rename/remove whitespaces etc