How to use a mapping document to link together 2 other files?

[emypassingham]

Hi,

I have 2 files and I need to find the difference between each line of data between the files. However, sometimes the names of the data rows are slightly different e.g. 'Cash' and 'Cash and cash equivalents'. Therefore, it wasn't recognising that these should be compared. Is there a way to use a 3rd mapping document where I can detail cash = cash and cash equivalents etc and link this in to be used? Or is there another way please?

Many thanks.

[Sebastiaandb]

@emypassingham  

You can do that. 

First join both files and lets say that the R input is the one that has the correct name format (f.e. Cash). From the J output of the join you'll have all the rows that DO match. From the R and L outputs you'll get the rows that won't match. 

Second, after the first join you can just put a text input tool with two columns: in the first column (NAME1) you put Cash, in the second column you put 'Cash and cash equivalents' (NAME2). Now you can join this text input tool to the L output, you take NAME2 from the L input in the join and take NAME2 from the text input tool to make the join. In the join you select the field NAME1 from the text input tool and deselect NAME2 from both joined (L and the text input). Now you can join this again to the original R input and you should be fine. 

Of course you can also use fuzzy match for this but that eats a lot of performance so in my experience i tend to avoid that haha. 

[danilang]

Hi @emypassingham 

Here's an example.

The mapping file has a list of accounts as listed and the actual account names

You join this to the 2 input files on the account column and then take the value from the Actual Account column

Dan

[messi007]

@emypassingham,

You can take a look on the find and replace tool as well:

Best regards,

[emypassingham]

Thanks everyone! 

What about if I wanted to compare the total of figures from two of the lines from one file and one file on the other e.g. from input file 1, line 'cash' and line 'credit', compared to in input file 2 'cash and cash equivalents'?

Thanks.

[danilang]

Hi @emypassingham 

Any solution will be highly dependent on the structure of your data files.  Can you provide a couple of sample files?

Dan

[emypassingham]

Hi,

I have attached a sample of the data on this spreadsheet - what I need to calculate is outlined on sheet 1, File 1 data is on sheet 2, and File 2 data is on sheet 3.

Thanks for your quick help.

[Sebastiaandb]

@emypassingham I guess this is what you want :-). 

It calculates the differences between the two files after standardizing the categories. There's a bit of @danilang 's mapping solution in it tough ;-). 

Good luck!