Challenge #23: Parsing CPG Data

[GeneR]

The link to last week’s challenge (challenge #22) is HERE. 

Restructuring poorly formatted data is one of the most common Alteryx use cases.  For this challenge we will attempt to straighten out some Very poorly formatted data that is representative of an actual customer request.

The use case: A CPG company is having difficulty managing its data for analytics. The data they receive via an excel spreadsheet contains empty rows and misaligned fields.

The objective:  reformat the data in order to enable the analytics team to spend less time staging the data and more time doing analytics.

Update: As of 9/26/19, the start and solution file to this challenge have been updated.  Your solution will not match those posted by our Community members prior to this date. 

[alex]

Posting my version of the solution now behind the spoiler tag.  I used a tool out of the preparation group as well as a string function that I had not used before to come up with my solution.  Both came in quite handy!  I went the extra step to convert strings to numbers since I noticed $ in unit fields.  Thus the client now has the correct values for the unit type ( $ vs units).

Spoiler

I used the select records to specifically pull data out of the 2nd and 3rd rows to be able to identfy brand and headers

Continued - I used replacechar to convert values to negative by replacing ( with -,.  Then taking that data and removing the $, ), and commas and then wrapping that in a ToNumber formula.

[TaraM]

A solution to this one has been posted

Spoiler

[SeanAdams]

Very similar to @GeneR / @TaraM; and @alex - differences called out below.   Good challenge, thank you!

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[NicoleJohnson]

My solution! (This should be #50 for me!)

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[SeanAdams]

:-) nice!

That will make you the second person to hit this.    That's a real encouragement to others to get all the weekly challenges done!

[SeanAdams]

AWESOME!

Congratulations to @NicoleJohnson to be the second ever person to hit the 50 challenges completed badge!

It's going to be a race to the finish line to see who will be first to hit the final 75 challenges badge (the mythical Bergschrund Crevasse badge)

• 2 people have 50 completed
• 3 people have 30 completed
• 5 people have 15 completed

So - it's still all to play for - 65 challenges out there so far, and @JoeM is probably cooking up number 66 & 67 as we speak!

[NicoleJohnson]

Thank you @SeanAdams!! I'm SO excited about all the cool tricks I've picked up doing these challenges so far. And it's even more fun challenging myself to keep up with someone, so keep crushing those challenges and I will see you (and hopefully a few others) at the top! :)

[estherb47]

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[LordNeilLord]

I'm sure I made this a lot more complicated than i needed to be but looking at other solutions mine seems pretty simple!

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.