Alteryx - Eliminations based on 2 columns

[MiroPwC]

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.

[atcodedog05]

Hi @MiroPwC 

Here is you can identify counterparties DR/CR and check whether its netting out. You can use join tool, with join on Counterparty-maps-Party & Party-maps-Counterparty  this way you will get Counterparty's DR/CR. 

Next you can perform calculation [DR/CR]+[Counterparty DR/CR] to check whether they are balancing (i,e sum=0).

Hope this helps 🙂

[atcodedog05]

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 🙂

[MiroPwC]

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]

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 🙂