script Query

[#1]

hi all,

i have a qvd and in script i need to write  a where clause on 1 column with multiple selections. what is the shortest way to write:

load * from test.qvd(qvd)

where

country in ('AU','GB','US','CA')

and currency not in ('USD','EUR','GBP')

please advise the right syntax for the above where clauses

thanks in advance

[alexandros17]

LOAD *
FROM test.qvd (qvd)

where

(country  = 'AU' or country  = 'GB' or country  ='US' or country  ='CA') and

(currency <> 'USD' and currency <>'EUR' and currency <>'GBP')

hope it helps

[#3]

thanks Alex

but this answer i know but its a long cut.

is there a shortcut code as the list of countries i have is 25 and then teh where cluase really becomes messy

[er_mohit]

try rhis

load * from test.qvd(qvd)

where  match(country,'AU','GB','US','CA') and not match(currency ,'USD','EUR','GBP');

[alexandros17]

UNfortunately the "in" clause doesn't work ... you can use a workaround that is add two fields to use as flags so for example, on the same row of counties or currencies you need  set 1 in the new fields.

(this can be done even with a new table to join)

in this way the final where clause  only check new fields values 1 or 0