Challenge #23: Parsing CPG Data

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.

challenge_23_solution.yxzp

challenge_23_start_file.yxzp

Join

Preparation

Advanced

Data Analysis

Transform

Difficult

Accepted answers

All comments

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.

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

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.

post_placeholder.txt

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.

post_placeholder.txt

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

Spoiler

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

post_placeholder.txt

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.

post_placeholder.txt

Solution attached. Great challenge

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

post_placeholder.txt

Spoiler

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

post_placeholder.txt

We did something similar during our Data School training

Spoiler

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

post_placeholder.txt

Holey Moley this took an hour. Very satisfied with the clean result.

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

post_placeholder.txt

Challenging: usually, what takes longer for me is to visualize the steps in my mind. This time I had the idea in my mind, but found quite challenging to find the proper tools and order to get it done.

Spoiler

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

post_placeholder.txt

Spent a fair amount of time trying to make an iterative macro to loop through columns and then realized i was over complicating it.

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

post_placeholder.txt

and another

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

post_placeholder.txt

This one felt like it should have been easier...but had to really think it thru....maybe I took the long road to get there.

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

post_placeholder.txt

My solution.

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

post_placeholder.txt

philipmannering

Phew! This was a long one. Solution attached.

Spoiler

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

post_placeholder.txt

Solution attached.

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

post_placeholder.txt

I took a very ham-fisted approach to this one, but at least it worked? I think I could make it dynamic based on what other people did...

Spoiler

Week 23.png

Here's my solution. Took a bit of lateral thinking to get everything shaped correctly, but this was good fun.

Spoiler

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

post_placeholder.txt

This was hard. I had to refer to the solution. My lack of ability to solve this was frustrating but I realize this is part of the learning process. I'll go back and study this again. The solution is like a rubic's cube... doing things that on the surface doesn't seem to be bringing the solution closer, but in the end, one last twist and there it is.

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

post_placeholder.txt

Different approach to others I've seen. Probably less manageable in terms of scalability so should have used the transpose tool but works and is simple to understand - which hopefully counts for something!

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

post_placeholder.txt

Challenge Completed

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

post_placeholder.txt

Had to refer back to solution. Pretty difficult without referencing. Learning curve was steep for this one.

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

post_placeholder.txt

Probably made this solution waaaayyy more complicated than it had to be... but it works 😉

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

post_placeholder.txt

Spoiler

just noticed that I solved this challenge months ago, but never handed it in. Not the most dynamic solution, though, but works.

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

post_placeholder.txt

This was a fune one! Solution attached

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

post_placeholder.txt

Somewhat different approach than the given solution.

Spoiler

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

post_placeholder.txt

My Solution

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

post_placeholder.txt

#23 Solution

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

post_placeholder.txt

Here's my solution! Was quite challenging 🙂

Spoiler

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

post_placeholder.txt

MAAbdullahAlMubarah

Ok, Shall we do a time series analysis? what is the target field? Is it on weekly basis?

Quick Slick Nick

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

post_placeholder.txt

Quick Links

Unanswered
Community Events
Groups

Tags

This months top contributors

Hollingsworth 335