topic Re: Left Join Issues in QlikView

Left Join Issues

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I'm doing something incredibly simple that I've done hundreds of times before, and I can't for the life of me work out what's going wrong...

I have 4 tables that I'm joining into one table called DATA.

My script is as follows:

DATA:

'2017' as Year,

'July' as Point,

'Second' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM A;

DATA:

'2017' as Year,

'July' as Point,

'First' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM B;

LEFT JOIN (DATA)

'2017' as Year,

'July' as Point,

'Second' as Phase,

ID,

Group

FROM C;

LEFT JOIN (DATA)

'2017' as Year,

'July' as Point,

'First' as Phase,

ID,

Group

FROM D;

What I would expect to see is:

Year	Point	Phase	Status	ID	Surname	Firstname	Gender	Group
2017	July	Second	Current	123	Webb	Jessica	F	AR
2017	July	Second	Current	687	Bennett	Tomas	M	VF
2017	July	First	Current	354	Frapwell	Jane	F	EW
2017	July	First	Current	752	Edwards	Freda	F	BF

What I actually see is:

Year	Point	Phase	Status	ID	Surname	Firstname	Gender	Group
2017	July	Second	Current	123	Webb	Jessica	F	AR
2017	July	Second	Current	687	Bennett	Tomas	M	VF
2017	July	First	Current	354	Frapwell	Jane	F	-
2017	July	First	Current	752	Edwards	Freda	F	-

What am I missing?? Why aren't my groups from the final table joining properly?

If I remove 'LEFT JOIN (DATA)' from the last table I get:

Year	Point	Phase	Status	ID	Surname	Firstname	Gender	Group
2017	July	Second	Current	123	Webb	Jessica	F	AR
2017	July	Second	Current	687	Bennett	Tomas	M	VF
2017	July	First	Current	354	Frapwell	Jane	F	-
2017	July	First	-	354	-	-	-	EW
2017	July	First	Current	752	Edwards	Freda	F	-
2017	July	First	-	752	-	-	-	BF

Which tells me the source data is ok...

Re: Left Join Issues

prat1507 — Fri, 28 Jul 2017 09:48:00 GMT

Use it like this maybe

DATA:

'2017' as Year,

'July' as Point,

'Second' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM A;

LEFT JOIN (DATA)

'2017' as Year,

'July' as Point,

'Second' as Phase,

ID,

Group

FROM C;

DATA:

'2017' as Year,

'July' as Point,

'First' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM B;

LEFT JOIN (DATA)

'2017' as Year,

'July' as Point,

'First' as Phase,

ID,

Group

FROM D;

Re: Left Join Issues

effinty2112 — Fri, 28 Jul 2017 09:50:18 GMT

Hi Jessica,

Try:

DATA:

'2017' as Year,

'July' as Point,

'Second' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM A;

DATA:

'2017' as Year,

'July' as Point,

'First' as Phase,

'Current' as Status,

ID,

Surname,

Firstname,

Gender

FROM B;

Noconcatenate

JoinData:

LOAD

'2017' as Year,

'July' as Point,

'Second' as Phase,

ID,

Group

FROM C;

Concatenate(JoinData)

Load

'2017' as Year,

'July' as Point,

'First' as Phase,

ID,

Group

FROM D;

Left Join(Data)

Load * resident JoinData;

Drop Table JoinData;

I think you need to concatenate your joining data altogether first before joining.

Cheers

Andrew

Re: Left Join Issues

arvind_patil — Fri, 28 Jul 2017 11:15:59 GMT

HI Jessica,

I got your issue In last try to join as below:

Year&'-'&Point&'-'&Phase&'-'&ID As % Key

It may serve your purpose.

Thanks,

Arvind Patil

Re: Left Join Issues

santiago_respane — Fri, 28 Jul 2017 12:51:28 GMT

Hi Jessica, i would go with Andrew Walker's suggestion. You need to first concatenate tables C and D before joining the to the DATA table. This is a general definition when joining multiple data into one table.

Hope this helps.

Kind regards,

Re: Left Join Issues

jessica_webb — Fri, 28 Jul 2017 13:18:50 GMT

Thanks Andrew, that's worked perfectly!

Now need to read up on concatenating to find out why and apply it to the rest of my script

Re: Left Join Issues

jessica_webb — Fri, 28 Jul 2017 13:19:14 GMT

Thanks Santiago - appreciate your reply

Re: Left Join Issues

effinty2112 — Fri, 28 Jul 2017 13:54:00 GMT

Hi Jessica,

Glad to have helped!!