Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Same-Same-Different

sdemichelis
7 - Meteor

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

ABC1234A1
ABC1234A2
ABC1234A2
ABC1234A3
DEX2456A4
DEX2457A5
DEX2458A6
DEX2458A6
DEX2458A6
DEX2458A9

 

Results

 

Document Project

ABC1234A1
ABC1234A3
DEX2458A6
DEX2458A9

 

Any ideas or any links to tips would be great

Thank you

 

9 REPLIES 9
Qiu
20 - Arcturus
20 - Arcturus

@sdemichelis 
It will be better if you could elaborate the meaning of "same-same-different".
I have assume then made something like below.

0915-sdemichelis-1.PNG

sdemichelis
7 - Meteor

@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

TSP
8 - Asteroid

@sdemichelis,interested to know whether you think

 

ABC1234A2

 

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.

 

2021-09-19 11-51.PNG

sdemichelis
7 - Meteor

@TSPthank you. Both solutions work for what I am looking for. One is more restrictive than the other and both brilliant. Many thanks!

Qiu
20 - Arcturus
20 - Arcturus

@sdemichelis 
Glad to help and thank you for the accept mark.

TSP
8 - Asteroid

To make it even leaner, you could employ the CReW Macro set, and use the Only Unique tool:

2021-09-19 12-40.PNG

TSP
8 - Asteroid

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

cparis
5 - Atom

Do you know from the example above to get an output like:

 

Document    Project

ABC123       A1, A2, A3

TSP
8 - Asteroid

@cparisyou would add another Summarise tool at the end as follows:

 

- Group by [Document]

- Concat [Project]. Use ", " as the separator (comma space)

 

 

2021-10-06 0800.PNG

Labels