How to make a matching test between 2 files

Question

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)

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

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

Data.xlsx

DataPCN.xlsx

MarqueeCrew · Accepted Answer

@Sousou,

Here is your result in a picture:

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

LJoonas · Accepted Answer

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