Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #23: Parsing CPG Data

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

alex
11 - 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
TaraM
Alteryx Alumni (Retired)

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
SeanAdams
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus

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.

SeanAdams
17 - Castor
17 - Castor

:-) 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
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora
Spoiler

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

LordNeilLord
15 - Aurora

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.