ACT NOW: The Alteryx team will be retiring support for Community account recovery and Community email-change requests Early 2026. Make sure to check your account preferences in my.alteryx.com to make sure you have filled out your security questions.
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
Not sure I understand the rationale for having a merchant moved to Merchant2 column if it was the customer's only store, but got it nonetheless.
All,
Spoiler
My solution was about the same as others, albeit I could have used the Dynamic Rename.
-I was also experimenting with the Bridge Table Macro, to no avail as I think that there was a way to use it vice all of the Transpose tools.
-Cheers!
Matt
-I was also experimenting with the Bridge Table Macro, to no avail as I think that there was a way to use it vice all of the Transpose tools.
-Cheers!
Matt
Fortunately, I've just come against this exact problem with a client, so I had a head start:
Spoiler
First, I transposed the data grouping by UserID and Count, I then joined the stream to itself on these two fields to create the first and second merchant on the same row. I then filtered where these two values were the same. I then gave each row a recordID:
I then took a stream and transposed again, grouping by RecordID, UserID, Count and Name (Merchant Number), and created a title field to use in the crosstab. I then sorted the data by the grouped fields and alphabetically by the Value field, which is the name of the merchant. I then cross-tabbed the data grouping by RecordID, UserID and Count, with the new title "Check" used as the header, and the value being a concatenated value string of both merchant names. This would mean they would be in the same order for each direction, so I can now highlight the records where merchant 1 and merchant 2 are the same combination the other way around:
I then joined this stream back to the original stream on recordID, dropping all fields from this stream. This simply highlights the duplicated but reversed combinations.
I then simply sorted accordingly and dropped the additional fields.
I then took a stream and transposed again, grouping by RecordID, UserID, Count and Name (Merchant Number), and created a title field to use in the crosstab. I then sorted the data by the grouped fields and alphabetically by the Value field, which is the name of the merchant. I then cross-tabbed the data grouping by RecordID, UserID and Count, with the new title "Check" used as the header, and the value being a concatenated value string of both merchant names. This would mean they would be in the same order for each direction, so I can now highlight the records where merchant 1 and merchant 2 are the same combination the other way around:
I then joined this stream back to the original stream on recordID, dropping all fields from this stream. This simply highlights the duplicated but reversed combinations.
I then simply sorted accordingly and dropped the additional fields.
M.
I've had to self-join data before but the requirement to group the data by customer flummoxed me for a bit. I eventually came upon a simple solution:
