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.
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.
Solution that scales for multiple scenarios
I was puzzled why all combinations of store visits were not used. Anyways, I solved for the answers provided.
slightly different solution to the others provided - trick used is...
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.