community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #11: Identify Logical Groups

Highlighted
Alteryx Alumni (Retired)

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. The link to the solution for last challenge #10 is HERE. For this exercise let’s look at some simple text mining that can be performed with Alteryx. There are several ways to do this challenge, I will provide one solution that uses a batch macro and one that does it without. It is a great example to see how batch macros can simplify a workflow.

 

The use case:

 

A manufacturing company receives customer complaint data on a daily basis from their call centers about the medical parts they distribute to their customers. The company monitors these comments to understand which parts and part groups have the highest complaint rate. This helps the company prioritize which parts to focus on from a development standpoint.

 

In this exercise, take the customer complaint data (Field_6 in the Test2 data) and identify which bucket the complaint falls within. The complaint can fall into multiple buckets and needs to be flagged as these complaints take highest priority. Create an aggregate view of which buckets or bucket pairings have the highest # of complaints.

 

This is only a subset of data so all records will not be assigned to buckets and can be ignored.

 

This exercise is a packaged Alteryx module due to the size of the input file. Double click it after saving the attachment and it should extract and open in Alteryx.

 

UPDATE 2/8/2016:

This article has been updated with 2 Solutions.

Creative Director
Creative Director

This article has been updated with 2 different solutions.

Tara McCoy
Bolide

I love comparing all these solutions and learning more about how to utilize different tools.  I'd like to see an explanation on what the search macro is doing.  I've attached a screen shot of my workflow in the spoiler window.  It works great if you know how many words you are looking for.  On my machine, it finished in under 3 seconds as compared to 10 secs for solution 1 and 12 secs for solution 2.  :manhappy:

 

 

Spoiler


In looking at the Solution 1, I learned that I could've eliminated the sort and multi-row tool I used and used the concatenation option in the first summarize tool.  I tested that approach on my workflow and it also worked and took the same amount of time.  I found the tricky part to be how to introduce the search items if you don't know how many you will have.  I also included a test to ensure each complaint ticket only had one row of data to be on the safe side.  I'm happy to send a packaged workflow to anyone who wants it.

Week 11 Solution image.PNG


 

Alteryx Certified Partner

 

 Hi, 

 

I have also tried to do this faster. I used full cross join and then just filter items which contain search.

And it runs very quickly.

 

Spoiler
Weekly - 11.png

 

Asteroid

Playing catch up here with the weekly challenges.  Similar solution to Martin's.

 

Spoiler
Capture.JPG
Aurora
Aurora

similar solution to the 2 provided - differences in approach in the spoiler below

 

Spoiler
- used a regEx to clean up punctuation
- Then used a regEx to tokenise each word into a new row
- Trimmed out repeating words
- used Find/Replace instead of a Join (removes the need to set uppercase to join)
... from there on - same solution as provided

Ex11.png


Alteryx Certified Partner
Alteryx Certified Partner

I am pleased with my results :)

 

Spoiler
Capture.PNG

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

My solution, similar to others.

 

Spoiler
WeeklyChallenge11.JPG
Alteryx Certified Partner

I wasted a lot of time on this weeks challenge trying to figure out how to parse the complaint field. In the end, I gave up and found a solution that didn't involve parsing the text.

Spoiler
challenge_11.jpg
Quasar