Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 -
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
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;