

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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...
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Santiago - appreciate your reply


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jessica,
Glad to have helped!!
