Challenge #49: Reformatting Transactional Data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The link to last week’s challenge is HERE.
This week’s exercise is another example of how Alteryx can take poorly formatted data such as transactional log files and turn it into usable data.
Use Case: A customer has some data that comes with key product information stored at the top of the file. Each data column contains three lines of header information per product (product, market and type). The customer wants this information to be shown in rows for each product.
Objective: Reformat the input data to match the output example.
- Labels:
- Basic
- Core
- Data Preparation
- Join
- Preparation
- Transform
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
A solution has been posted (it looks just like yours @Joe_Mako). Thanks for sharing yours too, @MichelKars.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
...and here's another alternative with a hard-coded shortcut. Only works insofar as 1-to-1 relationship between Product, Market and Type remains valid.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Michael, I like the simplicity of your solution. Any chance I could see the .yxmd? I'm at dupton@decisionlab.net. My solution (just posted) also works, but uses hard-coding based on observed values...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@DanielUpton, with our latest release of Community, you can now upload solutions right into the thread and no longer have to share just via spoilers!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I think my solution is the same as some others.
I then used the Transpose and Crosstab tools to pivot the data into the correct format, using the first three rows as a reference file. This was then linked back on the header type:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator