Challenge #49: Reformatting Transactional Data

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.

challenge_49_start_file.yxmd

challenge_49_solution.yxmd

Join

Preparation

Data Preparation

Core

Transform

Basic

Accepted answers

All comments

Joe_Mako

Spoiler

MichelKars

Spoiler

My solution slightly different but gives the same results.

TaraM

A solution has been posted (it looks just like yours @Joe_Mako). Thanks for sharing yours too, @MichelKars.

Spoiler

2016-11-21 08_44_49-Alteryx Designer x64 BETA - DataPrep_ReformattingData_Solution.yxmd.png

DanielUpton

Spoiler

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

DanielUpton

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

mceleavey

I think my solution is the same as some others.

Spoiler

I separated the input into two streams by using the sample tool to select the first three rows, and skip the first three rows respectively.
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:

challenge_49_solution.yxmd

SeanAdams

very similar to all the others. Seems that this may be the natural choice (given that so many folk arrived at this independantly)

challenge_49_SeanSolution.yxmd

NicoleJohnson

My solution. Oh, Transpose & Cross-Tab, whatever did I do before I figured out how to use you...

Spoiler

challenge_49_NicoleJohnson.yxmd

estherb47

Same solution as many. Split into 2 streams, one for the data, one for the key rows/headers.

challenge_49_EHB_solution.yxmd

LordNeilLord

Solution:

Spoiler

challenge_49_LNL.yxmd

A_Twa

challenge_49_start_file_AJT.yxmd

MsBindy

Happy to see my solutions are starting to look like everyone else's

challenge_49_Ms_Bindy.yxmd

nick_ceneviva

Solution attached

challenge_49_Ceneviva.yxmd

jamielaird

Here's my very long and clumsy solution! Not sure why but I couldn't get my head around this one.

Spoiler

challenge_49_JL.yxmd

patrick_digan

Spoiler

challenge_49_start_file.yxmd

data_rachel

Spoiler

Spent far too long messing around with those pivots trying all combinations of all the transform tools and finally did it in a fiddly way.... Will refer to how everyone else has done it and get my head around how to pivot data properly!

challenge_49_start_file.yxmd

marcreid

Solution

Spoiler

challenge_49_MR.yxmd

Natasha

Similar solution, though I used Find and Replace instead of Join.

Spoiler

challenge_49_Natasha.yxmd

LalaLele66

Another one finished! Normally I would have used the TRIM formula, but with version 11.5, the red/ green bar told me what was wrong, so that prompted me to try the Data Cleansing formula instead.

challenge_49_Reformatting_Transactional_Data.yxmd

samN

pretty much the same as the solution. Another one down

challenge_49.yxmd

ggruccio

Several steps to get there - getting great practice with these tools!

challenge_49_gg_finish.yxmd

SGolnik

This one reminds me of the data I get at work LOL

challenge_49_mysolution.yxmd

LandonG

Solution attached.

challenge_49_start_file.yxmd

philipmannering

My solution is attached.

Spoiler

challenge_49 - Reformatting Transactional Data.yxzp

jasperlch

Solution attached.

challenge_49_JL.yxmd

msicak

Struggled with this longer than I'd like to admit, but got there in the end.

Spoiler

challenge_49_MS.yxmd

BrendaS

Last one for today.

challenge_49_Brendas solution.yxmd

derekbelyea

Spoiler

challenge_49_JDB_AnalytixStudio.yxmd

blairmbailey

Solution attached - thanks!

challenge_49_BB.yxmd

JoshKushner

Nice simple one

challenge_49_start_file.yxmd

JosephSerpis

Challenge Completed

Challenge_49_Joe_Serpis.yxmd

TeePee

Spoiler

I found this challenge super tricky and ended up getting the final result with a convoluted hack of a workflow. Clearly, I need more Crosstab practice!

challenge_49_start_file_TP.yxmd

SueDonim

I struggled with this one and had to get some ideas from Joe_Mako's approach.

Spoiler

I had been close, but had trouble with the cross-tab and transpose tool settings. I had tried just about everything but "Name"

And frankly, I still don't see why my lower sample tool provided 255 records, when I had selected the first 3 (plus grouping on F1), but it gave me what I needed - a bit of luck really.

challenge_49_MySolution.yxmd

verathut

Spoiler

challenge_49_start_file_VT.yxmd

wardboston

Solution

Spoiler

challenge_49_solution_ashwin.yxmd

p_padham

Hi everyone!

Here is my solution:

Spoiler

challenge_49_completed.yxmd

SophieJoanna

Hello - I have been following this solution through and I may be doing it wrong but the output in your solution doesn't seem to include the date - is that correct? Any help would be appreciated.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317