Hi all,
I am trying to compare multiple rows and calculate matching result according to logic. For example,
No| Entity | Counterparty | P/O No | Currency| Amount | Transaction Date | Matching Result
1 | A | B | 123AB | USD | 1000 | 04/Mar/20 | Matched
2| B | A | 123AB| USD |-1000 | 04/Mar/20 | Matched
3| B | A | 123AB| USD | -1000 | 04/Mar/20 | Unmatched
This is ideal result. The logic is,
a) Entity-Counterparty set of 2 record should be same (e.g. A,B),
b) P/O number should match
c) Sum of amount should be less than 1
d) Transaction Date should be in the same month.
e) Currency should be same
When there are 2 line with same P/O number, I can use aggr (sum(Amount),P/O No, Currency) to evaluate the matching.
However, the issue is
1) There can be duplicate data like line 2 and line 3
2) Some P/O can share same P/O no. For example, C and D can have a transaction under 123AB as well.
3) P/O No should be case insensitive. 123AB should match with 123ab or 123Ab or 123aB.
Moreover, I also have a limitation that I can only access to the chart expression. Hence, I should handle this logic in the straight table chart expression. I am struggling with a lot of aggr, set analysis, if-else...
Could you share any idea on this? Thank you!
