Create a filtered table with WHERE NOT EXIST

Anonymous — Fri, 16 Mar 2012 15:33:35 GMT

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

Create a filtered table with WHERE NOT EXIST

Fri, 16 Mar 2012 17:24:28 GMT

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!

topic Create a filtered table with WHERE NOT EXIST in QlikView

Create a filtered table with WHERE NOT EXIST

Create a filtered table with WHERE NOT EXIST