left join based on multiple fields

[#1]

hi!

I have 2 tables that have 3 columns in common. i want to add the 4th column [DELTA] in table 2 to table 1 if the first 3 columns are the same as in table 1. here is what i wrote:

table_1:

LOAD
     LIQROW as PRICE,

     AMOUNT,

     BRANCH_CD as SPEED,

     UBR_CD as ID,

     CCY,

     FROM

[filename.xlsm]

(ooxml, embedded labels, table is [table 1]);

left join (table_1)
LOAD
     DELTA,

      BRANCH_CD as SPEED,

     UBR_CD as ID,

     [adjusted LCR LINE] as PRICE

[filename.xlsm]

(ooxml, embedded labels, table is [table 2]);

but it looks like certain rows in table 1 are double counted. do i need to add where clause?

[swuehl]

Are there any duplicate records in table 2 with same values for the three key fields? This would explain the multiplication of records in the joined table.

If yes, you need to decide how you want to handle this. Do these records show the same DELTA or a different DELTA?

[#3]

‌hi Vivienne

bphased on your script, table 2 will be left joined to table 1 based on only 2 columns - branch_cd and ubr_cd.

at the end of the script table 1 should contain all fields from table 1 originally as well as delta and price.

what do you mean by double counted? Can you give example?

[#4]

table 1:

A	B	C	D
1	iii	lv	555
2	kkk	gucci	666
3	lll	other	222

A	B	C	E
1	iii	lv	12
2	kkk	other	3
3	mmm	no brand	4

I want to have everything in table 1 and add column e in table 2 to table 1 if all the values in col. a,b,c in table 2 match those in table 1. so my expectation is to get the below table after the code:

A	B	C	D	E
1	iii	lv	555	12
2	kkk	gucci	666	0
3	lll	other	222	0

but D looks like was double counted.. how can I get the above table?

table_1

LOAD A,

B,

C,

D

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (table_1)

LOAD A,

B,

C,

E

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

[#5]

Hi Vivienne,

Your script looks fine. I would check the data if I were you.

If column D amount exactly double up, then chances are you have more than one row in table_2 with value a,iii,lv for column A, B, C respectively.

[swuehl]

Try a mapping approach:

MAP:

MAPPING

LOAD A&B&C, E

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

table_1

LOAD A,

B,

C,

D,

APPLYMAP('MAP', A&B&C, 0) as E

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

[#7]

hi Elly,

thank you so much for your reply! I checked my file and yes you are right. for table 1, it has multiple rows with the same values in col. A,B,C as it has total 11 columns and other columns distinguish the different rows. table 2 doesn't have that. if that's the case, should I use map instead of left join?

thanks,

Vivienne