topic Equivalent of Oracle IN function in QlikView

Equivalent of Oracle IN function

darrellbutler — Wed, 27 Apr 2011 12:22:34 GMT

In my load script I only want to load records that that meet a certain criteria i.e:

LOAD *

from Services

WHERE

MID(NOTE_TEXT,1,4) IN (

'FM02', 'FM03', 'FM04', 'FM05', 'FM06', 'FM07',

'FM08', 'FM09', 'FM10', 'FM11', 'FM12', 'FM13',

'FM14', 'FM15', 'FM16', 'FM17', 'FM18', 'FM19',

'FM21', 'FM22', 'FM23', 'FM24', 'FM26', 'FM28',

'FM29', 'FM30', 'FM31', 'FM32', 'FM33', 'FM35' )

hOW CAN THIS BE ACHEIVED IN qv ?

Any thoughts ?

Many thanks

pat_agen — Wed, 27 Apr 2011 12:27:45 GMT

hi,

one way is to use the "match" function:

match( str, expr1 [ , expr2,...exprN ] )
The match function performs a case sensitive comparison.

Example:
match( M, 'Jan','Feb','Mar')

returns 2 if M = Feb

returns 0 if M = Apr or jan

so for your case

load *

from Services

where match(mid(NOTE_TEXT,1,4),'FM02','FM03' etc. etc.)>0

brenner_martina — Wed, 27 Apr 2011 12:42:19 GMT

Hi,

... and wildmatch uses *:

... where wildmatch(Mid(NOTE_TEXT,1,4),'FM02',...,'FM09','FM1*','FM2*');

darrellbutler — Wed, 27 Apr 2011 12:53:37 GMT

Excellent Martina - just the job !

darrellbutler — Wed, 27 Apr 2011 12:54:10 GMT

Pat Agen - brilliant works well !