topic Re: WHERE NOT EXISTS - script assistance in QlikView

WHERE NOT EXISTS - script assistance

ashmitp869 — Thu, 17 Mar 2022 11:53:20 GMT

Hi there,

Please kindly assist with my below script - I am not able to use not exists function properly -

SE:

LOAD

SE."Derived National Service Event Record Id",

1 as count_se,
1 as count_moh_se

from QVD;

NAP_MoH_SE_TMP:
LOAD DISTINCT
"OOS.Derived National Service Event Record Id" ,//as "SE.Derived National Service Event Record Id",
1 AS SE.count_se,
0 AS SE.count_moh_se
RESIDENT OOS;

Concatenate (SE)
//TMP:
LOAD
"OOS.Derived National Service Event Record Id" as "SE.Derived National Service Event Record Id",
SE.count_se,
SE.count_moh_se
RESIDENT NAP_MoH_SE_TMP;

//WHERE NOT EXISTS ("SE.Derived National Service Event Record Id","OOS.Derived National Service Event Record Id" );

DROP TABLE NAP_MoH_SE_TMP;

I am getting this output -

where as I want this -

Re: WHERE NOT EXISTS - script assistance

ashmitp869 — Fri, 18 Mar 2022 00:31:24 GMT

Hi All,

I have manage to write the script -

SE:

LOAD

SE."Derived National Service Event Record Id",

SE."Derived National Service Event Record Id" as SE.UniqueTestField,

1 as count_se,
1 as count_moh_se

from QVD;

Concatenate(SE)
LOAD DISTINCT
"OOS.Derived National Service Event Record Id" as "SE.Derived National Service Event Record Id",
1 AS SE.count_se,
0 AS SE.count_moh_se
RESIDENT OOS
WHERE NOT EXISTS ("SE.UniqueTestField","OOS.Derived National Service Event Record Id");

SE_OOS_count:
LOAD
"SE.Derived National Service Event Record Id",
SE.count_se as SE.count_se_final,
SE.count_moh_se as SE.count_moh_se_final
RESIDENT SE;

I am using the expression below to get the result : sum(SE.count_se_final)

But my requirement is to show SE.month - Jul and Service Events as 547. How to get that.

I understand , why the 89 count is coming null as it taking from RESIDENT OOS table which is does not exists in SE.

But is there any way to show - Jul with the number 547 ?

Thanks in advance

Re: WHERE NOT EXISTS - script assistance

QFabian — Fri, 18 Mar 2022 00:56:18 GMT

Hi @ashmitp869 , i cant see the month or date field in your script, but please try if something like this works for you :

SE_OOS_count:
LOAD
"SE.Derived National Service Event Record Id",
SE.count_se as SE.count_se_final,

if(isnull(Month) or Month = '' or Month = 0, peek(New_Month), Month) as New_Month, //check if the date is null and then put the previous value from the same field.

SE.count_moh_se as SE.count_moh_se_final

RESIDENT SE;