Looking up invoice data set against 2 sources

Hi,

I receive invoices from vendors (in easily usable excel format) and i need to check the rates in the invoice against 2 databases ( really just excel files). I need to add columns to this to return the billing rates in these 2 databases. The reason i can't combine these 2 databases is because one is based on employee names and the other is based on titles. Is i feel like there is a simple way to do this with Multiple Join but i can't for the life of me figure it out. Here is a picture of what i am trying to accomplish

Datasets

Best Practices

Join

Data Investigation

Accepted answers

markcurry

Check to see if you have any duplicate names your table, and your 2nd lookup table.

In the example above, if you had 2 x 'Smith, Jo' in your 2nd lookup table, it will Join both records with your data, so you would end up with 4 records in the Join, and the two unmatched records in the L output giving 6 records.

All comments

markcurry

If you Join your Green table above with your Lookup Table 1, you'll get the rates from Table 1 for 4 records, so use the Union tool to join the L and J outputs. (Could be the L or R, depending what way you have set up the Join, it will be one with 1 record).

Then do the same for your second Lookup Table.

krishnagandhi

So that is how i attempted to solve the issue, however after the second table is joined and unioned i end up with more records than i started with. i am thinking maybe it has something to do with the fact that the second table is a vstring look up, but i'm not sure. As you can see from the picture i have 17366 records after the first union, which is good, but after the second one somehow the number goes up to 17502 workflow 2.jpg

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703