topic Re: left join based on multiple fields in QlikView

left join based on multiple fields

Wed, 19 Aug 2015 21:19:37 GMT

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?

Re: left join based on multiple fields

swuehl — Wed, 19 Aug 2015 21:59:54 GMT

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?

Re: left join based on multiple fields

Wed, 19 Aug 2015 22:00:27 GMT

‌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?

Re: left join based on multiple fields

Thu, 20 Aug 2015 13:02:11 GMT

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

iii

555

kkk

gucci

666

lll

other

222

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

table_1

LOAD A,

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (table_1)

LOAD A,

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

Re: left join based on multiple fields

Thu, 20 Aug 2015 15:39:15 GMT

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.

Re: left join based on multiple fields

swuehl — Thu, 20 Aug 2015 16:07:14 GMT

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,

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

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

Re: left join based on multiple fields

Thu, 20 Aug 2015 16:08:51 GMT

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