Where clause

[sfloberg]

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'

[dsharmaqv]

try this

Load Table where string not equal to...?

[tcullinane]

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;

[rahulpawarb]

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

[mdmukramali]

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

[Anonymous]

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

[MarcoWedel]

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

[susovan]

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

[Anonymous]

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

[sasiparupudi1]

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