Challenge #25: Creating Merchant Combos

[kmcdaniel]

Spoiler

[Parul_Kapoor]

Solution attached.

[gracec97]

Spoiler

[Illya]

My solution. Not quite there but different to the standard solution and interesting enough to post, sometimes it's the journey that is the important bit (I would still use the standard solution for this sort of problem in future though).

[jbrowne02]

Interesting challenge. Easy to get the 77 lines but had to do some wacky rearranging at the end to match the solution.

[JamesCameron]

My Effort

Spoiler

[sarahjohnson]

Spoiler

[SueDonim]

This was trickier than I expected.  There's probably a simpler way.

Spoiler

Process:
- Transpose data on UserID and Count
- Extract the Merchant Number (the right-most digit of Name)
- Create StoreNum field and assign against Merchant Number such that 5 becomes 16, 4 becomes 8, 3 becomes 4, and 2 and 1 are unchanged
  -- This means that for addition of those numbers you will always get a unique combination
  -- In this case, it enables you to note that ABC | DEF is the same combination as DEF | ABC, so you can filter out the duplicate
- Join data onto itself so you have all combinations of Merchant1 and Merchant2
- Filter out instances where Merchant1 = Merchant 2
- Create field SumStore that is the Sum of the two StoreNums created above
- Sort by UserID (asc) and SumStore (asc)
- Multi-Row to flag duplicate (where for any UserID, SumStore has the same value as the previous record
- Filter out duplicates
- Sort data (probably unnecessary)
- Unique tool on UserID, Merchant1Value and Merchant2Value
- Where Merchant2Value is Null() swap record with Merchant1Value (solely for purposes of matching to official answer)
- Cleanup and sort

[sonyakasenkramer]

Spoiler