'NOT IN' function between 2 tables in QlikSense

[happydays1967]

Hi,

I am trying to create a count on id's that have 0-n records in another table. The filter would be that if a record for any of the filtered values does exist in the other table for a given id, this id will not be counted, regardless of other records with other values for the same id existing.

In SQL this would be

select count(distinct id) where not exists(select id from table2 where table2.id = table1.id and value in(<values>))

from table1

<values> of course should be dynamic in Qlik, based on a field filter. When I filter the needed values and then reverse the filter with 'select excluded values' I will still get the id's that do have the excluded value combined with any other value. I only want the id's that do not have a correpondance for the excluded value in the other table, regardless of the fact that they might have other values as well

ID		ID	Value
1		1	1
		1	2
		1	3
2		2	2
		2	3
3		3	1

I want to exclude all id's from table 1 where they don't have a value '2 ' in table 2. In this case only id 3. But with normal Qlik filtering it would render all records, because they have a value in the excluded value list.

I hope this makes any sense, would anybody know how to accomplish this in #QlikSense. I have tried to use set-analyses but that would give me an invalid column even though the syntax was ok.

Any help would be very much appreciated.

HP

[marcus_sommer]

It's not really clear for me what you want to do but if you want to count anything in regard to NULL you should replace the NULL with a real value like '<NULL>' in the script by using the NULL variables or measures like:

if(len(trim(Field)), Field, '<NULL>') or maybe coalesce(Field, '<NULL>')

The reason for such approach is that NULL isn't stored in any way within Qlik and couldn't be accessed or selected - at least not directly. Indirect ways are possible but often they increase the complexity more as it would be sensible.

With real values you may try something like this:

count({< ID = e({< ID = {'<NULL>'}>} ID) >} ID)

This may not be fulfilling your mentioned comparing of the ID's between multiple tables which couldn't be reached only per ID else it would require querying another unique fields from those tables, like:

count({< ID = e({< ID = {'<NULL>'}>} ID), OtherField = {"*"} >} ID)

Another way to compare fields from different tables is to name them differently  - a doubled field with a renaming is also a possibility.

[happydays1967]

@marcus_sommer thanks for thinking along, but it is not that simple unfortunately. Getting closer to the solutio though.... I will share if I figure it out!