Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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;
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
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
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*')
Where not ' '&Projectname&' ' like '* demo *'
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');
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
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