Where value is not in list

[JonathanKelly]

I am loading in a course list - I have a separate QVD which shows courses that are not required in my count.

How would I state in a load script that I want to load in all the course except where that course shows in my excluded QVD without just writing out where match(CourseId,...) and list out ID's?

[marcus_sommer]

Maybe something like this:

ExistsFilter: load CourseId from Exclude.qvd (qvd);

Final: load * from Final.qvd (qvd) where not exists(CourseId);

drop tables ExistsFilter;

[JonathanKelly]

That worked - however it only keeps the first instance of the non-removed courses.

I ran it using the below as you suggested and even looking into the not exists function it is the same. However, as ID must be called the same in both table it ends up not bring back all the data instead. If I reverse it to ask for where exists ID then it brings back everything however trying to reverse it doesn't. 

[marcus_sommer · Accepted solution]

In this case it needs additionally measurements, for example:

ExistsFilter: load CourseId as CourseIdExists from Exclude.qvd (qvd);

Final: load * from Final.qvd (qvd) where not exists(CourseIdExists, CourseId);

drop tables ExistsFilter;