topic Re: Where clause in QlikView

Where clause

sfloberg — Wed, 25 Nov 2020 16:16:04 GMT

I want to exclude the projectnames with 'demo' as one whole word. The only thing I have to go on is the word 'demo' because the user can enter whatever they want into the field.

I have the following table:

Table1:

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

];

How can I write a WHERE statement that only gives me the result:

'Test1'

'demonstration'

'Test3'

'Test democenter'

'Test demon'

Re: Where clause

dsharmaqv — Tue, 21 Feb 2017 12:19:21 GMT

try this

Load Table where string not equal to...?

Re: Where clause

tcullinane — Tue, 21 Feb 2017 12:34:19 GMT

Im sure there must be an easier way but you could;

Table1tmp:

LOAD * Rowno() as Row;

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

];

noconcatenate

ParsedTable:

Load *, subfield(Projectname,' ') as Subfield resident Table1tmp;

Load Row as ExcludedRow resident ParsedTable

Where match(Subfield,'demo');

Table1:

Load Projectname resident Table1tmp where not exists(ExcludedRow,Row);

Drop Table Table1tmp, ParsedTable;

Re: Where clause

rahulpawarb — Tue, 21 Feb 2017 12:46:02 GMT

Hello Stephanie,

Trust that you are doing well!

Please refer below given sample script:

Data:

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

]

WHERE NOT wildmatch( Projectname, 'demo','demo *','* demo', '* demo *');

Hope this will be helpful.

Regards!

Rahul

Re: Where clause

mdmukramali — Tue, 21 Feb 2017 12:56:51 GMT

Hi,

you can use string function to get the result:

Table1:

LOAD * INLINE [

Projectname

Test1

Test demo

Test demo Test

demonstration

Test3

demo

Test democenter

Test demon

]

Where Index(Projectname,'demo ')<1 and Projectname<>'demo' and Right(Projectname,5)<>' demo' ;

Thanks,

Mukram

Re: Where clause

Anonymous — Tue, 21 Feb 2017 13:16:16 GMT

Try the below, double wildcards (*) used either side of the word demo;

Data:

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

]

WHERE NOT wildmatch(Projectname, '*demo*')

Re: Where clause

MarcoWedel — Tue, 21 Feb 2017 17:38:18 GMT

Where not ' '&Projectname&' ' like '* demo *'

Re: Where clause

susovan — Wed, 22 Feb 2017 07:17:19 GMT

Hi,

You can also try this,

Table1:

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

]

Where not WildMatch(Projectname,'Test demo','* Test','demo');

Re: Where clause

Anonymous — Wed, 22 Feb 2017 07:25:22 GMT

Hi,

Try this below script, It will solve your problem.

Table1:

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

]

WHERE NOT wildmatch( Projectname, 'demo','demo *','* demo', '* demo *');

Thanks & Warm regards,

Venkata Sreekanth

Re: Where clause

sasiparupudi1 — Wed, 22 Feb 2017 07:36:00 GMT

Try

Temp:

Load

Projectname,

if (Lower(Projectname)='demo',1,

If( Index(Lower(Projectname & ' '),' demo ')>0,1,0)) as Flag;

LOAD * INLINE [

Projectname

'Test1'

'Test demo'

'Test demo Test'

'demonstration'

'Test3'

'demo'

'Test democenter'

'Test demon'

];

Temp1:

NoConcatenate Load

Projectname,

if (Lower(Projectname)='demo',1,

If( Index(Lower(Projectname & ' '),' demo ')>0,1,0)) as Flag

Resident Temp

where Flag=0;

Drop Table Temp;

HtH

Sas