Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have 2 tables and I need to take some data from One table - Table A - to the other - Table B.
Table A contains Employee ID's, Names and Types.
Table B contains Employee ID's ,Labor Costs and Labor Hours.
I would like to use table A to bring the Names and Types next to each Employee ID in Table B.
Left join brings all the Employee ID's, so I am getting in Table B empty lines for Employee ID's which exist in Table A but donwt exists in Table B. How can I avoid these empty lines?
In other words, I would like to use something similar to Vlookup in excel.
I know I can use Mapping Load but this clause allows only one filed for each Mapping Load, right?
Also, using Table A as a dimension is not useful to me because of other data in the script. That's why I need to bring the data into Table B rather than use a dimension to create the link between the two tables.
Thank you,
Sandra
Table A:
EmplyeeID | Name | EmployeeType |
---|---|---|
1 | AAA | type 1 |
2 | BBB | type 1 |
3 | CCC | type 1 |
4 | GGG | type 1 |
5 | HHH | type 1 |
7 | KKK | type 2 |
8 | WWW | type 2 |
9 | XXX | type 2 |
10 | YYY | type 2 |
11 | MMM | type 2 |
12 | NNN | type 2 |
13 | DDD | type 2 |
14 | EEE | type 2 |
Table B:
EmployeeID | PaidHours | LaborCost |
---|---|---|
1 | 221.15 | 9,335 |
2 | 197.80 | 16,946 |
3 | 197.80 | 18,231 |
4 | 197.80 | 15,001 |
5 | 33.41 | 2,529 |
6 | 195.53 | 14,799 |
7 | 125.90 | 11,205 |
8 | 228.38 | 10,922 |
9 | 219.43 | 9,908 |
10 | 274.03 | 18,449 |
11 | 200.80 | 8,837 |
Applymap() is usually faster than joining, so if you already have that there is not a lot of scope for improvement.
If the mapping loads take too long, you can combine them by string concatenating the fields being mapped (field1 & '|' & field2) and then using SubField with the ApplyMap to retrieve the desired value.
thanks. I will try both.
thanks again,