Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Athlete:
LOAD
AthleteID as all_AthleteID,
AthleteID as all_TookPartAthleteID,
/ HomeRunID as all_HomeRunID
FROM
Athlete.qvd
(qvd);
Result:
LOAD
TookPartAthleteID as all_AthleteID,
SeriesRaceEventKey as DTPSeriesRaceEventKey
FROM
TookPart.qvd
(qvd)
where exists(all_TookPartAthleteID);
I need to create a new table where all of the athletes in the Athlete table are not present in the Result table.
This table to be called did_not_table part
Tried various ways but I dont seem to get it right.
Any help most appreciated
In my experience, where not exists is very finicky with aliases. Here's how I ended up getting the results you are looking for (hopefully):
In the script I created inline tables to simulate your Athlete.qvd and TookPart.qvd because I do not have access to them on my end.
AthleteTemp:
NoConcatenate
LOAD * Inline [
AthleteID, HomeRunID
1, 1
2, 2
3, 3
4, 4
5, 5
];
Store * From AthleteTemp into Athlete.qvd;
drop table AthleteTemp;
ResultTemp:
NoConcatenate
LOAD * INLINE [
TookPartAthleteID, SeriesRaceEventKey
5, 3
6, 4
7, 8
];
Store * From ResultTemp into Result.qvd;
drop table ResultTemp;
Now Athlete and Result are in the same format that you have. So next you need to alias everything in the tables and store them into qvds.
Athlete:
NoConcatenate
LOAD
AthleteID as all_AthleteID,
AthleteID as all_TookPartAthleteID,
HomeRunID as all_HomeRunID
FROM
C:\Users\BApperson\Desktop\Athlete.qvd
(qvd);
STORE * FROM Athlete into AthleteFinal.qvd;
drop table Athlete;
Result:
NoConcatenate
LOAD
TookPartAthleteID as all_AthleteID,
SeriesRaceEventKey as DTPSeriesRaceEventKey
FROM
C:\Users\BApperson\Desktop\Result.qvd
(qvd);
STORE * FROM Result into ResultFinal.qvd;
drop table Result;
Now you just load these tables and perform the exists / not exists function in a normal fashion...
AthleteAliased:
NoConcatenate
LOAD all_AthleteID,
all_TookPartAthleteID,
all_HomeRunID
FROM
C:\Users\BApperson\Desktop\AthleteFinal.qvd
(qvd);
did_not_table:
NoConcatenate
LOAD all_AthleteID,
DTPSeriesRaceEventKey
FROM
C:\Users\BApperson\Desktop\ResultFinal.qvd
(qvd)
WHERE NOT EXISTS(all_AthleteID);
drop table AthleteAliased;
So now your resulting set will be in the did_not_table.
In this case the resulting set is just ID's 6 and 7 as they do not exist in the first table but 5 does so it is omitted.
Hope this helps!