Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello my QlikView friends. I am trying to load some data and filter out particular strings using wildcards. I wasn't sure if I should be using IF statements, so I was trying to use WHERE. I cannot get my script to work properly. Basically, I am trying to replicate the COUNTIFS formula from Excel as below in the load script of my QlikView application. Any help figuring this out is greatly appreciated
Excel:
=COUNTIFS(Tag,"*PC5*",Tag,"<>*APC5*",Tag,"<>*PC5A*",Tag,"<>*NPC5*",Line,"<>*GSOCLAIMS*",Queue,"<>*END*")
Here is what I have in QlikView
LOAD BUS_AREA AS Line,
QUEUECD AS Queue,
TAG_LINE_CMMT AS Tag
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is Data)
WHERE BUS_AREA <> 'GSOCLAIMS'
AND QUEUECD <> 'END'
AND TAG_LINE_CMMT Like '*PC5*'
AND TAG_LINE_CMMT <> '*APC5*'
AND TAG_LINE_CMMT <> '*PC5A*'
AND TAG_LINE_CMMT <> '*NPC5*';
Replace
AND TAG_LINE_CMMT Like '*PC5*'
AND TAG_LINE_CMMT <> '*APC5*'
AND TAG_LINE_CMMT <> '*PC5A*'
AND TAG_LINE_CMMT <> '*NPC5*'
With
AND WildMatch(TAG_LINE_CMMT,'*PC5*')
AND NOT WildMatch(TAG_LINE_CMMT, '*APC5*', '*PC5A*', '*NPC5*')
Replace
AND TAG_LINE_CMMT Like '*PC5*'
AND TAG_LINE_CMMT <> '*APC5*'
AND TAG_LINE_CMMT <> '*PC5A*'
AND TAG_LINE_CMMT <> '*NPC5*'
With
AND WildMatch(TAG_LINE_CMMT,'*PC5*')
AND NOT WildMatch(TAG_LINE_CMMT, '*APC5*', '*PC5A*', '*NPC5*')
Gysbert, you are the man!!! Thanks so much