Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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;
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
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
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,
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
Thanks Santiago - appreciate your reply
Hi Jessica,
Glad to have helped!!