Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

thnkgreen
Contributor

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
MVP & Luminary
MVP & Luminary

Re: Using WHERE not equal with a string value

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
2 Replies
MVP & Luminary
MVP & Luminary

Re: Using WHERE not equal with a string value

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
thnkgreen
Contributor

Re: Using WHERE not equal with a string value

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