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

HOW TO FIND A MATCH FROM TWO FIELDS IN TWO FILES

Lauren_Holmes
8 - Asteroid

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. 

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @Lauren_Holmes 

 

Would you have some sample data so it would be better to help you out?

 

Thanks,

sazit
6 - Meteoroid

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!

sazit_0-1610944584472.png

PS: Full credit goes to Ken Y for this idea!

 

Lauren_Holmes
8 - Asteroid

Hi, 

 

Please find attached two sample files. 

 

The idea is to find "true matches" identical SN/AN.

Find "True no match" - does not match either.

Identify some discrepancies in data - match SN, no match AN - match AN, no match SN.

 

Thank you.

Lauren_Holmes
8 - Asteroid

Hello,

 

This is great thank you 🙂

Is there a way that I am able to download this workflow from you?

 

Below are two sample data types.

 

 

sazit
6 - Meteoroid

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

Lauren_Holmes
8 - Asteroid

This is great, and exactly what i was after! Thank you 🙂 !

Labels