Alteryx - Eliminations based on 2 columns

Question

Hi guys,

I would like to ask you for a little help. I have uploaded the input file ("Input")

Is there a possibility to modify the input so it will highlight (maybe with same color) those rows which have same "Party & Counterparty" and "Counterparty & Party" ?

For instance, we can see that "Party" Calmar has positive balance 10 000 against "Counterparty" Diamond and that
"Party" Diamond has -10 000 against "Counterparty" Calmar which means they are netting.

I have also uploaded Desired Output how it should look so the user could easily identify where both parties and counterparties have the same balances or if there is a difference.

Thank you a lot.

Desired Output.xlsx

Input.xlsx

atcodedog05 · Answer

Hi @MiroPwC

Possibility might be name are not matching in your initial example some had Lim others Limited which i had fixed since it was data issue. Names needs to exactly if the join needs to work.

Hope this helps 🙂

MiroPwC · Answer

Hi atcodedog,

thanks for the solution 🙂 however when i use join like you I do not see any output in “J”. Don’t you know why ? Could it be due to older version of alteryx in my case or do you use some custom settings?

atcodedog05 · Answer

Hi @MiroPwC

Now for your requirement on setting each pair with same color. I was able to find this amazing response by @CharlieS Post where we can generate random color and use them for formatting.

Workflow:

1. In the filter tool i am filtering only the rows where net is balanced (i,e sum=0)

2. Setting record id as key column

3. Transposing Party & Counterparty columns with record id as key.

4. Using sort tool sorting by record id and value (party names). This way for each record id value is sorted ascending.

5. Using formula tool and setting a column with label "pair" which will be column name.

6. Using crosstab tool with record id as key, pair as column name, value as value and aggregation as concat. This way the party names are sorted ascending in the pair name.

7. Using unique tool to keep only unique pair.

8. Generating random color for each pair.

9. Using append to map pair coloring to main data.

10. Using filter tool to only keep rows where Party & Counterparty are present in the pair.

11. In Report table i am creating a column rule where assign color is not empty assign that color as background. Like below.

This will produce table like in first snapshot and can render to excel.

Hope this helps 🙂

Party_v2.yxmd