topic Re: script Query in QlikView

script Query

Fri, 14 Jun 2013 07:15:13 GMT

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

Re: script Query

alexandros17 — Fri, 14 Jun 2013 07:29:03 GMT

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

Re: script Query

Fri, 14 Jun 2013 07:32:13 GMT

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

Re: script Query

er_mohit — Fri, 14 Jun 2013 07:38:47 GMT

try rhis

load * from test.qvd(qvd)

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

Re: script Query

alexandros17 — Fri, 14 Jun 2013 07:40:03 GMT

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