10 Replies Latest reply: Jun 14, 2018 10:36 AM by Andrzej Kisielewicz RSS

    KPI with multiple nested conditions

    Andrzej Kisielewicz

      Hello Qlik community.

       

      I need to create KPI which calculates tickets (workorderid).

      KPI has several nested conditions.

      SQL query for this count looks as below:

       

      SELECT COUNT(wo.WORKORDERID) FROM wo

      LEFT JOIN ws ON wo.WORKORDERID=ws.WORKORDERID

      LEFT JOIN sd ON ws.STATUSID=sd.STATUSID

      LEFT JOIN wq ON wo.WORKORDERID = wq.WORKORDERID

      LEFT JOIN qd ON wq.QUEUEID=qd.QUEUEID

      LEFT JOIN tl ON wo.TEMPLATEID=tl.TEMPLATEID

       

      WHERE

      qd.QUEUENAME LIKE 'ServiceDesk%'

      AND (tl.templatename like '%awarie%' OR tl.templatename like '%default%' or tl.templatename like '%reset%')

      AND wo.title not like '%probid%' and sd.ispending=0

      AND wo.WORKORDERID NOT IN (SELECT wttd.workorderid FROM WorkOrderToTaskDetails wttd

                                 LEFT JOIN TaskDetails td ON wttd.TASKID=td.TASKID

                                 LEFT JOIN QueueDefinition qd ON td.GROUPID=qd.QUEUEID

                                 WHERE qd.QUEUENAME NOT LIKE 'Servi%' or td.taskid is null) ;

       

      KPI counts OK in version below:

       

      Count({$<QUEUENAME={'ServiceDesk*'}, TEMPLATENAME={'*awarie*','*default*','*reset*'}, TITLE-={'*probid*'}, ISPENDING={'0'}>} Distinct WORKORDERID)

       

      but whe I try to add two more conditions like below:

       

      Count({$<QUEUENAME={'ServiceDesk*'}, TEMPLATENAME={'*awarie*','*default*','*reset*'}, TITLE-={'*probid*'}, ISPENDING={'0'},

               QUEUENAME-={'Servi*'} or Len(Trim(TASKID))=0>} Distinct WORKORDERID)

       

      result is not OK.

       

      As you see there is subquery which excludes some workorderids from count and I don't know how to write it in KPI script.