topic Where value is not in list in App Development

Where value is not in list

JonathanKelly — Mon, 17 Mar 2025 10:51:18 GMT

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?

Re: Where value is not in list

marcus_sommer — Mon, 17 Mar 2025 11:11:21 GMT

Maybe something like this:

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

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

drop tables ExistsFilter;

Re: Where value is not in list

JonathanKelly — Wed, 26 Mar 2025 11:09:21 GMT

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.

Re: Where value is not in list

marcus_sommer — Wed, 26 Mar 2025 11:36:39 GMT

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;