LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: StuartOboler

Hello,
I am testing for duplications using inexact criteria, but I'm having trouble condensing the results to something managable as the results repeat themselves. For example if A and B match I get two results, that A matches B and that B matches A.

A simplified version of the result of a test is as follows:
A B
100 300
100 200
200 300
200 100
300 100
300 200

Columns A and B are both the same field in the source data (the unique id of that record.)

These results tell me:
a) that record 100 matches records 300 and 200.
b) that record 200 matches records 300 and 100.
c) that record 300 matches records 100 and 200.

If I can condense those results to 'unique' matches it would be great.
A B
100 300
100 200

Ideally I would like the results in the following structure
Group Record
1 100
1 200
1 300

Any help would be appreciated.

Thanks,
Stuart

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mgajdosik

Hi,

what you can do is order A and B so A will always be lower and B higher value:


if A>B then temp_A = B else temp_A = A
if A>B then temp_B = A else temp_B = B

emit *
override emit temp_A as A, temp_B as B


then you will have records where A=100,B=200 and A=200, B=100 looking exactly same and you can sort unique afterwards.

Marek

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: StuartOboler

Hi,
Thanks for the solution, it worked really well.

I'd like to extend the problem slightly. So that if we have

100 200
100 300
200 100
200 300
300 100
300 200

the solution you provided reduces this list down to

100 200
100 300
200 300

However I already know that 200 and 300 are related because they both belong to the group 100.

Attached are a lavastorm graph, csv with data, and a spreadsheet with an explanation of the problem.

Thanks,
Stuart
Attachments:
Question on duplicates.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Stuart,

I saw that you reposted an expanded question and am taking a look at how you might implement it within BRE.

Regards,
-Timon
MDA Support

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: StuartOboler

Hi Timon,
Any updates?
Regards,
Stuart

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Stuart, Apologies, I've had to juggle looking at this with other issues. The nature of the lookups here is a bit tricky. -Timon

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Hi Stuart, sorry for the delay. I believe this graph will do what you are looking for.

Cheers
Rich
Attachments:
Duplicate Question.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Rich,

This is a very slick solution. I had looked at this for a moment yesterday, but this was not the way I would have solved it. What type of algorithm or mathmatical principal did you use to figure this out.

Larry

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: StuartOboler

Hi Rich,
Thanks for the solution ... it seems so simple once someone else has solved it.
Cheers,
Stuart

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Glad I could help Stuart; sorry I didn't take a look at this sooner. And Larry, thanks! I didn't really use any principle or algorithm, I guess. I just went in thinking it was sort of a transitive relationship you were looking for, and that it needed to be a relation across rows related by the first key, so started noodling with those thoughts in mind.

It was really the strength of the tool that let me solve it quickly. Once I had that thought, it was just trying a few things, checking the outputs, and working through it interactively.

Cheers
Rich