How to make a matching test between 2 files

[Sousou]

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

[MarqueeCrew · Accepted solution]

@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 solution]

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

[Sousou]

@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 :)