Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using WHERE not equal with a string value

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*';

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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*')


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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*')


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Gysbert, you are the man!!!  Thanks so much