Hi, I have used the join function on multiple occasions to find matches between two data sets using one common field, however I am faced with a new project where i need to ideally identify a match using two common fields.
EXAMPLE - SERIAL NUMBERS, and ASSET NUMBERS.
FILE 1 CONTAINS,
SERIAL NUMBER - ABCD, ASSET NUMBER - EFGH
SERIAL NUMBER - 1234, ASSET NUMBER - 5678
FILE 2 CONTAINS,
SERIAL NUMBER - ABCD, ASSET NUMBER - EFGH
SERIAL NUMBER - 1234, ASSET NUMBER - 9876
Is there a way to create a flow so it will say... have an output of MATCH SERIAL AND ASSET/ NO MATCH SN, NO MATCH AN, NO MATCH EITHER, as on some occasions the SERIAL NUMBERS may match but the ASSET NUMBERS from the files may not?
Ideally I would have on my output, a tab that says
1. MATCH SERIAL NUMBER AND ASSET NUMBER
2. MATCH SERIAL NUMBER
3. MATCH ASSET NUMBER
4. NO MATCH TO EITHER
I initially thought i would simply do a join on Serial first, then a join on asset however some Equipment's have different serials to assets and the data was getting matched incorrectly.
Any help or guidance would be great.
Solved! Go to Solution.
Hi Lauren
A good way to find all sets 4 sets of groupings for your two datasets with two primary fields could be daisy chain joins like the following where you initially join on both fields, then both one of the primary fields from the drop off records, then the other primary field from its previous drop off records. Finally the drop offs after the third join would be not matched on either key fields. Hope it makes sense!
PS: Full credit goes to Ken Y for this idea!
Hi Lauren,
Yep here it is.
You will notice that the output some of the values from the second file are present. This will have to be present as these values are different to the one in the first file. If you primarily wanted to compare with the first file as the base, then you can remove the fields from the second file.
Cheers
Sajit
This is great, and exactly what i was after! Thank you 🙂 !