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

[CreepyCatLady]

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:

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.

[ArnadoSandoval · Accepted solution]

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,

[Ksrinivasan]

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