Paired matches from 2 sources

[aka_ash]

Hi,

I am working on workflow that performs matching on several fields for large volumes of data.

I built the matching in the workflow around the Join tool then reconciling the joins to the source to split into individual rows with an additional status of "Match" if matched.

I have an issue where i might have 2 records in a source that matches against a single record that meets all the matching criteria.
I would prefer that the excess record\s not flag as a match, so i only see pairs of records that have matched from both sources, the excess would flag as not matched.

Is there any modification i can make to flag as excess matching records as not matching?

I have some sample data and a screenshot of sample workflow for this scenario as well, the 3rd and 4th record in source 1 is the item matching against a single record in source 2.

Thanks in advance,

Ash

Source	Data 1	Data 2	Data 3	Data 4	Data 5
1	abc123	Apples	87623	aw	ee-878
1	qwe321	Oranges	324234	aw	fd-234
1	asd876	Grapes	555543	aw	gg-44
1	asd876	Grapes	555543	aw	gg-44
1	lpo345	Apples	345	bb	jh-123

Source	D-1	D-2	D-3	D-4	D-5
2	abc123	Apples	87623	aw	ee-878
2	qwe321	Oranges	324234	aw	bb-234
2	asd876	Grapes	555543	aw	gg-44
2	lpo345	Apples	345	bb	jh-123

[grazitti_sapna · Accepted solution]

Hi @aka_ash , will something like this work for you?If this is not what you are looking for kindly attach a desired output for better understanding.

And, if this is what you are trying to achieve kindly mark this post as solution.

Thanks.

[Qiu]

@aka_ash 
Can we just use the Unique tool first before joining? it would be simple and easy to understand.

[OllieClarke]

Hey @aka_ash if you don't want any rows that match multiple rows in the other source to come through, then I'd recommend using the Only Unique CReW Macro on both streams like this:

hope that helps

[atcodedog05 · Accepted solution]

Hi @aka_ash 

As per what i could understand by your words "flag excess matching records as not matching" if there are duplicate rows to want to match 1st duplicate row of source 1 to source 2, since there is 2nd duplicate row in source 1 and not in source 2 it should be in unjoin.

Input : the table provided by you.

Outputs

Join:

Left-unjoin:

Right -unjoin:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Qiu]

@aka_ash 
yes, please mark our solutions as accepted.

[aka_ash]

Thanks @grazitti_sapna, @OllieClarke, @Qiu,@atcodedog05

I believe the Multirow formula @grazitti_sapna, will work for my scenario as will the tile approach i think with some tweaking @atcodedog05.

Unfortunately regarding the other 2 solutions would not work for my scenario as i still need all the data to go through the matching.

Thanks again all,

Ash

[Qiu]

@aka_ash 
Thanks for the comment. Good to to know it get solved.

[atcodedog05]

Happy to help 🙂 @aka_ash 

Cheers and Happy Analysing 😀