Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this :
Count({$<QUEUENAME={"ServiceDesk*"}, TEMPLATENAME={"*awarie*","*default*","*reset*'", TITLE-={"*probid*"}, ISPENDING={'0'},
QUEUENAME={"=-{"Servi*"} or Len(Trim(TASKID))=0"}
>} Distinct WORKORDERID)
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)
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"}
Can u alter it as follow and try?
QUEUENAME={"=not wildMatch(QUEUENAME, 'Servi*' ) or Len(Trim(TASKID))=0"}
From what I see, this won't still work... you cannot have two different set analysis on the same field omarbensalem
Unfortunately your suggestion gives number over 3 times more than correct one
Would you be able to share a sample app to test this out?
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?
I am not sure how to do it in Qlik Sense, but feel free to share 80MB doc.
