Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

Challenge #23: Parsing CPG Data

Highlighted
Alteryx Alumni (Retired)

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. 

Highlighted
Bolide

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
week23-1.PNG
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.
week23-2.PNG
Highlighted
Creative Director
Creative Director

A solution to this one has been posted

Spoiler
2016-05-02 16_57_58-Alteryx Designer x64 - Week_23_Advanced_DataParsing_CPGData_Solution.yxmd.png
Tara McCoy
Highlighted
Nebula
Nebula

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.

Highlighted
Magnetar
Magnetar

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

 


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

Highlighted
Nebula
Nebula

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

Highlighted
Nebula
Nebula

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!

 

 

 

 

 

Highlighted
Magnetar
Magnetar

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

Highlighted
Magnetar
Magnetar
Spoiler

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

Highlighted
Alteryx Certified Partner

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

 


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