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 #25: Creating Merchant Combos

GeneR
Alteryx Alumni (Retired)

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

 

As we have said before, restructuring poorly formatted data is one of the most common Alteryx use cases.  This week’s challenge is another real world example of a data problem faced by one of our customers.

 

Use Case: A credit card company’s customer Data is structured so that each row of data contains the merchants each customer has visited in a given week. The credit card company is wanting to understand the correlation of merchants visited by customer. For example: if a customer visit 7-11 what other stores do they have a high propensity to visit?

 

Objective:  Restructure the data into rows that pair merchants together by customer.

dataMack
12 - Quasar

Could these be posted in 10.1 format for a while?  I know 10.5 came out last week, but haven't had a chance to upgrade yet.

GeneR
Alteryx Alumni (Retired)

You could open the YXMD file in word pad or another text editor and in the header change 10.5 to 10.1 it should open for you then.

TaraM
Alteryx Alumni (Retired)

Hi @dataMack - I've uploaded a 10.1 version. As @GeneR points out, you need only change the version in a text editor. In this case you need to specify 10.0 though.

Tara McCoy
alex
11 - Bolide

The speed in which you can prototype, test and modify your workflow to achieve your desired result is one of many reasons Alteryx is so darn cool and useful.   I tackled this problem in 2 different manners.  The fixed format solution assumes you will only have 5 columns of merchant data.  The second solution will let you scale to allow for more columns of merchant data to be added without having to modify the workflow.   To test my assumptions, I added a new column of data and it worked perfectly.  As a way to use a tool I hadn’t used before, I threw the results from the first example into the Contingency Table tool from the Data Investigation tool category to create a matrix style chart to view the data from a different analytical perspective.

 

Fixed Format

Spoiler
The 2 extra tails are there to visualize the results in a table vs. a list

week25wf1.PNG
Homemade Table
wk25c1.PNG

From Contingency Tool
wk25c2.PNG

Solution that scales for multiple scenarios

Spoiler

week25wf2.PNG
TaraM
Alteryx Alumni (Retired)

A solution has been uploaded, suitable for version 10.1

Spoiler
2016-05-16 09_27_42-Alteryx Designer x64 - Analytics_CreatingMerchantCombos_Intermediate_Solution.yx.png
Tara McCoy
markp201
8 - Asteroid
Spoiler
Capture.JPG
MarqueeCrew
20 - Arcturus
20 - Arcturus

@JoeM,

 

I was puzzled why all combinations of store visits were not used.  Anyways, I solved for the answers provided.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SeanAdams
17 - Castor
17 - Castor

slightly different solution to the others provided - trick used is...

 

Spoiler
Only keep unique merchants by user
Put a unique ID on each
Then do a cartesian join and...
filter out all combinations where the left ID < the right ID (to eliminate X-X joins or the issue where you have X-Y as well as Y-X - i.e. the symmetrical version of a good row)

NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution. Results are slightly different because it made more sense to me to have the single vendors show up as Merchant1 instead of Merchant2... but other than that, results match. This was a fun one! Slightly bizarre... and I can decide if I'm pleased by the solution or mildly confused by it. But at least I'm fairly certain my final solution should be adaptable to varying #'s of merchants. 

 

 

Spoiler
1. Generate Rows based on Count for each UserID
2. Remove Merchant from column name, and then use Multi-Field tool to match if CurrentFIeldName > RowCount, put results in new field named as column # with Merchant2 as suffix.
3. Transpose, Key field = UserID
4. Add "Merchant1" to fields not already containing Merchant2 in values
5. RegEx parse Merchant (1 or 2) and MatchOrder (prefix)
6. Join Data to itself by UserID/RowCount/Merchant
7. Filter + Formula + Unique + Summarize tools combine to get rid of duplicates
8. Sort & Select to remove unwanted columns

Phew!

WeeklyChallenge25.JPG