topic Re: Not IsNull If Statement in QlikView

Not IsNull If Statement

cbaqir — Mon, 05 Feb 2018 17:38:08 GMT

I am trying to count the number of populated Resource fields per DEFECT_ID in order to use them as a grouping on a bar chart. Can someone please help me fix this? It is not working as intended. As you can see from the table below, the counts are not accurate. 4046 should show 4 but it shows 8.

='vBuildResourceCount: ' & sum(if(Not IsNull(CCL_RESOURCE),1,
if(Not IsNull(CHARGES_RESOURCE),1,
if(Not IsNull(CLIN_DOC_RESOURCE),1,
if(Not IsNull(CHARGES_RESOURCE),1,
if(Not IsNull(CORE_RESOURCE1),1,
if(Not IsNull(CORE_RESOURCE2),1,
if(Not IsNull(CPOE_RESOURCE),1,
if(Not IsNull(HIM_RESOURCE),1,
if(Not IsNull(LAB_RESOURCE),1,
if(Not IsNull(MPAGE_RESOURCE),1,
if(Not IsNull(MPAGE_CUSTOM_RESOURCE),1,
if(Not IsNull(PHARMACY_RESOURCE),1,
if(Not IsNull(PROVIDER_DOC_RESOURCE),1,
if(Not IsNull(RADIOLOGY_RESOURCE),1,
if(Not IsNull(RULES_RESOURCE),1,
if(Not IsNull(SURGERY_RESOURCE),1,
0)))))))))))))))))

Re: Not IsNull If Statement

rubenmarin — Mon, 05 Feb 2018 18:04:58 GMT

Hi Cassandra, maybe with this expression:

Sum(Aggr(RangeSum(

Not IsNull(CCL_RESOURCE),

Not IsNull(CHARGES_RESOURCE),

Not IsNull(CLIN_DOC_RESOURCE),

Not IsNull(CORE_RESOURCE1),

Not IsNull(CORE_RESOURCE2),

Not IsNull(CPOE_RESOURCE),

Not IsNull(HIM_RESOURCE),

Not IsNull(LAB_RESOURCE),

Not IsNull(MPAGE_RESOURCE),

Not IsNull(MPAGE_CUSTOM_RESOURCE),

Not IsNull(PHARMACY_RESOURCE),

Not IsNull(PROVIDER_DOC_RESOURCE),

Not IsNull(RADIOLOGY_RESOURCE),

Not IsNull(RULES_RESOURCE),

Not IsNull(SURGERY_RESOURCE)

),DEFECT_ID))*-1

Re: Not IsNull If Statement

MK_QSL — Mon, 05 Feb 2018 18:18:05 GMT

Use this

='vBuildResourceCount: ' &

RangeSum(COUNT(Distinct if(Not IsNull(CCL_RESOURCE),CCL_RESOURCE))

,COUNT(Distinct if(Not IsNull(CHARGES_RESOURCE),CHARGES_RESOURCE))

,COUNT(Distinct if(Not IsNull(CLIN_DOC_RESOURCE),CLIN_DOC_RESOURCE))

+COUNT(Distinct if(Not IsNull(CHARGES_RESOURCE),CHARGES_RESOURCE))

+COUNT(Distinct if(Not IsNull(CORE_RESOURCE1),CORE_RESOURCE1))

+COUNT(Distinct if(Not IsNull(CORE_RESOURCE2),CORE_RESOURCE2))

+COUNT(Distinct if(Not IsNull(CPOE_RESOURCE),CPOE_RESOURCE))

+COUNT(Distinct if(Not IsNull(HIM_RESOURCE),HIM_RESOURCE))

+COUNT(Distinct if(Not IsNull(LAB_RESOURCE),LAB_RESOURCE))

+COUNT(Distinct if(Not IsNull(MPAGE_RESOURCE),MPAGE_RESOURCE))

+COUNT(Distinct if(Not IsNull(MPAGE_CUSTOM_RESOURCE),MPAGE_CUSTOM_RESOURCE))

+COUNT(Distinct if(Not IsNull(PHARMACY_RESOURCE),PHARMACY_RESOURCE))

+COUNT(Distinct if(Not IsNull(PROVIDER_DOC_RESOURCE),PROVIDER_DOC_RESOURCE))

+COUNT(Distinct if(Not IsNull(RADIOLOGY_RESOURCE),RADIOLOGY_RESOURCE))

+COUNT(Distinct if(Not IsNull(RULES_RESOURCE),RULES_RESOURCE))

+COUNT(Distinct if(Not IsNull(SURGERY_RESOURCE),SURGERY_RESOURCE)))

Re: Not IsNull If Statement

cbaqir — Mon, 05 Feb 2018 19:13:43 GMT

Thanks!