topic Re: LEFT JOIN DOUBLES THE COUNT in QlikView

LEFT JOIN DOUBLES THE COUNT

Wed, 19 Aug 2015 21:08:54 GMT

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

iii

555

kkk

gucci

666

lll

other

222

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;

how can I get it work?

Message was edited by: vivienne li

Re: LEFT JOIN DOUBLES THE COUNT

swuehl — Wed, 19 Aug 2015 21:50:56 GMT

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

Re: LEFT JOIN DOUBLES THE COUNT

qlikviewwizard — Thu, 20 Aug 2015 00:29:06 GMT

What is your required output? Thank you.

Re: LEFT JOIN DOUBLES THE COUNT

Thu, 20 Aug 2015 01:45:56 GMT

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,

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;

will give you:

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

jjj

555

kkk

gucci

666

lll

other

222

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,

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

jjj

iii

555

kkk

gucci

other

666

lll

other

no brand

222

Let me know if it's help.

Cheers.

Eva

Re: LEFT JOIN DOUBLES THE COUNT

jagan — Thu, 20 Aug 2015 01:59:26 GMT

HI,

Try like this

Temp:

LOAD A,

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_1;

LEFT JOIN (Temp)

LOAD A,

FROM [FILE.XLSM]

OOXML, embedded labels, table is table_2;

Data:

LOAD

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.

Re: LEFT JOIN DOUBLES THE COUNT

jagan — Thu, 20 Aug 2015 02:00:25 GMT

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

Regards,

Jagan.

Re: LEFT JOIN DOUBLES THE COUNT

Thu, 20 Aug 2015 12:58:58 GMT

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

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 DOUBLES THE COUNT

swuehl — Thu, 20 Aug 2015 16:08:13 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 DOUBLES THE COUNT

maxgro — Thu, 20 Aug 2015 17:57:54 GMT

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: