Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III

Left Join Issues

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:

YearPointPhaseStatusIDSurnameFirstnameGenderGroup
2017JulySecondCurrent123WebbJessicaFAR
2017JulySecondCurrent687BennettTomasMVF
2017JulyFirstCurrent354FrapwellJaneFEW
2017JulyFirstCurrent752EdwardsFredaFBF

What I actually see is:

YearPointPhaseStatusIDSurnameFirstnameGenderGroup
2017JulySecondCurrent123WebbJessicaFAR
2017JulySecondCurrent687BennettTomasMVF
2017JulyFirstCurrent354FrapwellJaneF-
2017JulyFirstCurrent752EdwardsFredaF-

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:

YearPointPhaseStatusIDSurnameFirstnameGenderGroup
2017JulySecondCurrent123WebbJessicaFAR
2017JulySecondCurrent687BennettTomasMVF
2017JulyFirstCurrent354FrapwellJaneF-
2017JulyFirst-354---EW
2017JulyFirstCurrent752EdwardsFredaF-
2017JulyFirst-752---BF

Which tells me the source data is ok...

1 Solution

Accepted Solutions
effinty2112
Master

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

View solution in original post

7 Replies
prat1507
Specialist

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
Master

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
Partner - Specialist III

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
Specialist

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
Creator III
Author

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
Creator III
Author

Thanks Santiago - appreciate your reply

effinty2112
Master

Hi Jessica,

Glad to have helped!!