SUM(IF()) in Load Script

[nwilliams]

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

[tresesco]

Are you using Group By?

[marcus_sommer]

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