Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CreepyCatLady
Contributor III
Contributor III

Equivalent to "where not exists," but in a measure

Hello all. I have tried to search the forums for an answer to this but I have not found one.

I have two fields that come from different tables; I need to show as a measure in a chart a count of values that only exist in one of the two fields. For example:

CreepyCatLady_0-1610659309894.png

I would only want to count items that do not appear in "Completed Items", so my count should be 3. I do not want to eliminate C,D, and F from my entire data set, only from one chart, so I can't use NOT EXISTS in the script, I have to do this calculation in my set analysis for the measure. I have tried multiple ways and nothing has worked. Can anyone help with this?

Thanks.

1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

Hi @CreepyCatLady 

I got it working using Left Join and null handling (see this topic: Replace nulls ); This is the script, very simple by loading your two tables, followed by a temporary table (Result_Temp) storing the Left Join results, followed by configuring the null handling prior to creating the Result table and dropping the Result_Temp.

Table_A:
Load * Inline [
All_Items
A
B
C
D
E
F
];

NoConcatenate
Table_B:
Load * Inline [
Completed_Items
C
D
E
];

NoConcatenate
Result_Temp:
Load All_Items As Completed_Items
Resident Table_A;

Left Join Load *, 1 As Exist Resident Table_B;

set NullInterpret =''; // '' are interpreted as NULL
Set NullValue='n/e';   // Null values will be replaced with the text 'Data_Missing'
NullAsValue Exist;  // the fields to apply the NullInterpret and NullValue 

NoConcatenate
Result:
Load Completed_Items As Result_Item,
     Exist
Resident Result_Temp;

Drop Table Result_Temp;

The KPI expression counting the "Not completed items" is:

Count({<Exist={'n/e'}>} Exist)

I am attaching a demo application file: Z_Where_Not_Exist_01.qvf

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

2 Replies
ArnadoSandoval
Specialist II
Specialist II

Hi @CreepyCatLady 

I got it working using Left Join and null handling (see this topic: Replace nulls ); This is the script, very simple by loading your two tables, followed by a temporary table (Result_Temp) storing the Left Join results, followed by configuring the null handling prior to creating the Result table and dropping the Result_Temp.

Table_A:
Load * Inline [
All_Items
A
B
C
D
E
F
];

NoConcatenate
Table_B:
Load * Inline [
Completed_Items
C
D
E
];

NoConcatenate
Result_Temp:
Load All_Items As Completed_Items
Resident Table_A;

Left Join Load *, 1 As Exist Resident Table_B;

set NullInterpret =''; // '' are interpreted as NULL
Set NullValue='n/e';   // Null values will be replaced with the text 'Data_Missing'
NullAsValue Exist;  // the fields to apply the NullInterpret and NullValue 

NoConcatenate
Result:
Load Completed_Items As Result_Item,
     Exist
Resident Result_Temp;

Drop Table Result_Temp;

The KPI expression counting the "Not completed items" is:

Count({<Exist={'n/e'}>} Exist)

I am attaching a demo application file: Z_Where_Not_Exist_01.qvf

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

Ksrinivasan
Specialist
Specialist

hi,

Condition is you want to make in Measures:

in KPI use below expression.

CAT1=All items, CAT2=Completed items

Count(CAT1)-Sum(IF(CAT1=CAT2,'1',''))

 

Ksrinivasan