LEFT JOIN DOUBLES THE COUNT

[#1]

Hi,

I'm new to Qlikview. trying to link 2 tables together. here are how the tables look

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	lll	no brand	4

table 2:

I want to add col. E in table 2 to col. D in table 1 if col. A,B,C are the same in both tables.

sorry for the confusion!! made some changes: B should be iii.

EDIT:

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

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;

how can I get it work?

Message was edited by: vivienne li

[swuehl]

Not sure if I understand the issue.

Your sample data above does not seem to have common key values in both tables. So there shouldn't be E values joined to table_1. Do you see joined values with these data? And what do you mean with 'doubles the count'?

[qlikviewwizard]

What is your required output? Thank you.

[#4]

Hi Vivienne,

Qlikview will use as a key all fields with a common name.

First you need to determine what is your key.

The Key MUST be unique. If not you will get duplication.

Let's assume you want join on the A field.

table_1

LOAD A,

B,

C,

D

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (table_1)

LOAD A,

E

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

will give you:

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

Let's assume now your key is A,B and C.

You will have duplication because the values in B and C are differents in table 1 and 2.

Another option is the renaming technic:

table_1

LOAD A,

B,

C,

D

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (table_1)

LOAD A,

B as B_in_T2,

C as C_in_T2,

E as E_in_T2

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

A	B	B_in_T2	C	C_in_T2	D	E
1	jjj	iii	lv	lv	555	12
2	kkk	kkk	gucci	other	666	3
3	lll	lll	other	no brand	222	4

Let me know if it's help.

Cheers.

Eva

[jagan]

HI,

Try like this

Temp:

LOAD A,

B,

C,

D

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (Temp)

LOAD A,

B,

C,

E

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

Data:

LOAD

A,

B,

C,

E AS D

RESIDENT Temp;

DROP TABLE Temp;

In both the tables only first row is matching, so you will get 12 in Column D other values are null.

Hope this helps you.

Regards,

Jagan.

[jagan]

Note: The combination of ABC should be unique in the Table 1, otherwise you will get duplicate records.

Regards,

Jagan.

[#7]

apologize for the confusion. there were some typos in the original post.

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	lll	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;

[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;

[maxgro]

if you have duplicate A B C in T1 but not in T2, there isn't any problem; 5 records in T1 at the beginning of the script and 5 records at the end

// dupl

T1:

load * inline [

A,B,C,D

1,iii,lv,555

1,iii,lv,555

1,iii,lv,555

2,kkk,gucci,666

3,lll,other,222

];

// no dupl

T2:

load * inline [

A,B,C,E

1,iii,lv,12

2,kkk,other,3

3,lll,no brand,4

];

left join (T1)

load A, B, C, E

Resident T2;

T1:

if you have duplicated (and matching) A B C in T2 you have duplicated records also at the end of the script execution (3 records in T1 at the start of the script, 4 at the end)

// no dupl

T1:

load * inline [

A,B,C,D

1,iii,lv,555

2,kkk,gucci,666

3,lll,other,222

];

// dupl

T2:

load * inline [

A,B,C,E

1,iii,lv,12

1,iii,lv,1200

2,kkk,other,3

3,lll,no brand,4

];

left join (T1)

load A, B, C, E

Resident T2;

T1: