community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to make a matching test between 2 files

Highlighted
Asteroid

Hello Alteryx community ! :)

 

I want to make a matching test between 2 files, to be sure that the number account of the Data.xls file will be compliant with the correct number account in DataPCN.xls

For example this is the kind of data i have to process: (data.xls)

 data.PNG

 

 

Now i want to check all the number of the "ACCOUNT" column with the "ACCOUNT_PCN" column of the file dataPCN.xls as you can see below

 

dataPCN.PNG

 

Now i don't know how to make a test matching for exemple ACCOUNT: 1010000 with ACCOUNT_PCN: 101

We have a column with the number of digit if we need to use it, but the Libelle column should not be taken for the matching..

 

If the account number match with the AccountPCN (compliant) 1010000 ==> 101 it's OK or 6133300 ==> 6133 etc

If there is no matching number i want to see data of the DATA.xls file that are not compliant with DATA_PCN.xls.. it's possible ? 

 

I hope you understand my request

 

Thank a lot for your help & sorry if i made mistakes with my english

Alteryx Certified Partner
Alteryx Certified Partner

@Sousou,

 

Here is your result in a picture:

 

capture.jpg

 

I used a Find Replace tool to append the "DataPCN" data to the "Data".  After that I used a JOIN to find all records.  I wasn't sure which way you wanted the matching so I gave you all possibilities.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

Hi,

 

If I understood correctly you want to see, if ACCOUNT_PCN column matches with the beginning of ACCOUNT column. I built a workflow that uses Find and Replace tool to check if ACCOUNT_PCN matches with the beginning of ACCOUNT (Find method is picked as Beginning of the field). Then with join tool the left outers are the account numbers from DATA.xlsx that don't match, joins are the ones that match and right outers are account_pcn numbers from DataPCN that don't match. This should work as long as the account numbers are unique in each file.

 

Hopefully this helps you.

 

Edit. Was too slow :P

Asteroid

@MarqueeCrew and @LJoonas

 

Wow ! it seems to work very very good ! I will try this in my working document where there are lot of rows and account number

I will come back to you if i have a problem that i can't resolve, but i think it will be ok :D

 

Thank's a lot for your help and rapidity 

 

Have a nice day :)

Labels