Hi All,
I have recently moved to Alteryx and it has been an interesting ride so far. Very positive and, at times, frustrating.
I am trying to extract records from a report where I want to find entries classified as same-same-different
For example (same accounting entry document but different project)
Source data
Document Project
ABC1234 | A1 |
ABC1234 | A2 |
ABC1234 | A2 |
ABC1234 | A3 |
DEX2456 | A4 |
DEX2457 | A5 |
DEX2458 | A6 |
DEX2458 | A6 |
DEX2458 | A6 |
DEX2458 | A9 |
Results
Document Project
ABC1234 | A1 |
ABC1234 | A3 |
DEX2458 | A6 |
DEX2458 | A9 |
Any ideas or any links to tips would be great
Thank you
Solved! Go to Solution.
@sdemichelis
It will be better if you could elaborate the meaning of "same-same-different".
I have assume then made something like below.
@Qiumany thanks for the prompt reply and I think your solution might work.
In the context of forensic auditing, the same-same-different test helps to identify which entries were made that share the same unique document (i.e. invoice or doc number) but different cost code (for instance). The underlying idea is to spot invoices booked more than once across different cost centres or projects or identify is accountants try to hide costs by splitting them across different accounts or cost centres.
I will try your solution on the larger set but I am pretty sure you have hit the nail on the head. Many thanks
@sdemichelis,interested to know whether you think
ABC1234 | A2 |
should be excluded from your results?
If you want it included as an exception (i.e Project A2 has also had Document ABC1234 posted to it), then take the result of @Qiu 's centre join, but add a Unique tool rather than a summarise tool. Select the Document field, project field, (and any others you may think are relevant).
The U ("unique") output of this tool would be my answer.
@TSPthank you. Both solutions work for what I am looking for. One is more restrictive than the other and both brilliant. Many thanks!
@sdemichelis
Glad to help and thank you for the accept mark.
To make it even leaner, you could employ the CReW Macro set, and use the Only Unique tool:
This would give you all duplicated Document ID's, which you could summarise however you wish.
They're available here:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CReW-Macro-Support/ta-p/465740
Do you know from the example above to get an output like:
Document Project
ABC123 A1, A2, A3
@cparisyou would add another Summarise tool at the end as follows:
- Group by [Document]
- Concat [Project]. Use ", " as the separator (comma space)