Create a filtered table with WHERE NOT EXIST

[Anonymous]

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

[#2]

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!