Left Join Issues

[jessica_webb]

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...

[prat1507]

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;

[effinty2112 · Accepted solution]

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

[arvind_patil]

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

[santiago_respane]

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,

[jessica_webb]

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

[jessica_webb]

Thanks Santiago - appreciate your reply

[effinty2112]

Hi Jessica,

Glad to have helped!!