KPI with multiple nested conditions

[Anonymous]

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.

[OmarBenSalem]

Try this :

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

QUEUENAME={"=-{"Servi*"} or  Len(Trim(TASKID))=0"}       

>} Distinct WORKORDERID)

[sunny_talwar]

May be this

Count({$<QUEUENAME = {"=(WildMatch(QUEUENAME, 'ServiceDesk*') and not WildMatch(QUEUENAME, 'Servi*')) or Len(Trim(TASKID)) = 0"}, TEMPLATENAME = {'*awarie*','*default*','*reset*'}, TITLE -= {'*probid*'}, ISPENDING = {'0'}>} Distinct WORKORDERID)

[Anonymous]

Omar,

I have modified a little your answear, added missing curly brackets }, right now it looks like this:

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

but it gives nothing - not even zero. When I modified it again:

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

I got zero in result.

I think problem is with condition

QUEUENAME={"=-{'Servi*'} or  Len(Trim(TASKID))=0"}

[OmarBenSalem]

Can u alter it as follow and try?

QUEUENAME={"=not wildMatch(QUEUENAME, 'Servi*' ) or  Len(Trim(TASKID))=0"}

[sunny_talwar]

From what I see, this won't still work... you cannot have two different set analysis on the same field omarbensalem‌

[Anonymous]

Unfortunately your suggestion gives number over 3 times more than correct one

[sunny_talwar]

Would you be able to share a sample app to test this out?

[Anonymous]

I think I can share qvf file with one sheet. I use data manager not load script.

Right now application is about 80MB. Is it a way to make it smaller or this size is OK?

[sunny_talwar]

I am not sure how to do it in Qlik Sense, but feel free to share 80MB doc.