topic Re: SUM(IF()) in Load Script in QlikView

SUM(IF()) in Load Script

nwilliams — Wed, 17 Feb 2016 08:15:16 GMT

Hi,

I'm trying to add the following in the load script, but getting "Error in expression" when reloading. Any ideas?

LOAD
Sum( IF(cmdb_ci_server.assigned_to = '', 1, 0) +
IF(cmdb_ci_server.name = '', 1,0) +
IF(cmdb_ci_server.dv_u_lifecycle_status = '',1,0)) AS cmdb_master.c_completion_score,
cmdb_ci_server.asset,
cmdb_ci_server.dv_asset ,

cmdb_ci_server.assigned_to,

cmdb_ci_server.name,

cmdb_ci_server.dv_u_lifecycle_status

Re: SUM(IF()) in Load Script

tresB — Wed, 17 Feb 2016 12:57:31 GMT

Are you using Group By?

Re: SUM(IF()) in Load Script

marcus_sommer — Wed, 17 Feb 2016 14:56:52 GMT

With your expression you didn't want to aggregate something you want only add various checks to a score. Therefore you didn't need the sum-function: condition1 + condition2 + condition3 as result would be enough - but I suggest to use the function rangesum (which has advantages in cases of NULL or errors) and maybe a slight different checking with len(trim()) which would catch blanks and empty strings and real NULL, like:

LOAD
RangeSum( IF(len(trim(cmdb_ci_server.assigned_to)) = 0, 1, 0) ,
IF(len(trim(cmdb_ci_server.name)) = 0, 1,0),
IF(len(trim(cmdb_ci_server.dv_u_lifecycle_status)) = 0,1,0)) AS cmdb_master.c_completion_score,
cmdb_ci_server.asset,
cmdb_ci_server.dv_asset ,

cmdb_ci_server.assigned_to,

cmdb_ci_server.name,

cmdb_ci_server.dv_u_lifecycle_status

- Marcus