How do you create a case when statement in the QV load script

[#1]

I have a field called PRICE that I want to segment out. In SQL i would do the following:

CASE

WHEN PRICE < 10 then '< $10'

WHEN PRICE between 10 and 15 then '$10 - $15'

WHEN PRICE between 15 and 20 then '$15 - $20'

WHEN PRICE between 20 and 25 then '$20 - $25')

ELSE '>$25'

END as [Price Segment]

How would I do this in the LOAD script in qlikview?

[Oleg_Troyansky]

In your case, you probably have to do it in 5 nested IF statements... Pretty ugly, but I don't think there is anything more elegant.

[Anonymous]

You could also set up an interval match table to match PRICE against to get the values in the correct brackets.

[#4]

Hi Johannes,

I'm not familiar with interval match tables. Can you point me in the right direction to where I can find step by step instructions on how to use this technique?

thanks,

[johnw]

I would seriously just write the nested IF:

if(PRICE<10,'< $10'
,if(PRICE<15,'$10 - $15'
,if(PRICE<20,'$15 - $20'
,if(PRICE<25,'$20 - $25'
,'> $25')))) as Range

But as far as intervalmatch goes, I think the below is what you want. I wouldn't do this, but you could. Just search for intervalmatch in the help for more information.

[Ranges]:
LOAD * INLINE [
Min,Max,Range
0,9.99,< $10
10,14.99,$10 - $15
15,19.99,$15 - $20
20,24.99,$20 - $25
25,,> $25
];

[Main Table]:
LOAD
...
PRICE
...
FROM SOME SOURCE
;
INTERVALMATCH (PRICE)
LOAD
Min
,Max
RESIDENT [Ranges]
;

Also worth considering, though the intervals end up the same width and it doesn't display exactly as you like, is this:

class(PRICE,5,'$') as Range

[#6]

John,

Your script is good. thanks, but can you help me with the syntax a bit. How do i include "and" statements in QV?

for example,

IF (Bottle > 0.75 and Bottle not in (1, 3, 16), '> 750mL)

How do i incorporate these types of sql elements into QV syntax?

thanks,

[johnw · Accepted solution]

The match() function takes the role of the SQL IN statement. Like so:

if(Bottle>0.75 and not match(Bottle,1,3,16), '> 750 mL')

[#8]

Thanks for your help. It worked!

[#9]

Hello,

I have a Table of CITY , When I go for match function I had to write some times 4 cities names, some times 6 cities name, what I am facing is I write again and again city names regarding different criterias, Is it possible that I bring a Field in the Match fucntion in which that particular fields contains all the city name and should match them on conditions, as such there would be no need for writng city names again and again. I mean I dont want to write city names in Match function again and again I just need a Field which should fetch city names and go for matching in Match function. For example it takes log if I have to write 25 city names, is there any simple solution that city be fetched in Match function without writing them again and again.

CITY:

London

New York

Maxico

and so on all the cities upto 100.

if (Match (City, 'London', 'New York', 'Maxico','Karachi','Mumbai')) as XYZ

in upper case I had to write all the names depending condition.

is it possible like: IF (MATCH(City)) as XYZ.

Khalid

[#10]

Hi There,

I have a Table of CITY , When I go for match function I had to write some times 4 cities names, some times 6 cities name, what I am facing is I write again and again city names regarding different criterias, Is it possible that I bring a Field in the Match fucntion in which that particular fields contains all the city name and should match them on conditions, as such there would be no need for writng city names again and again. I mean I dont want to write city names in Match function again and again I just need a Field which should fetch city names and go for matching in Match function. For example it takes log if I have to write 25 city names, is there any simple solution that city be fetched in Match function without writing them again and again.

CITY:

London

New York

Maxico

and so on all the cities upto 100.

if (Match (City, 'London', 'New York', 'Maxico','Karachi','Mumbai')) as XYZ

in upper case I had to write all the names depending condition.

is it possible like: IF (MATCH(City)) as XYZ.

Best Regards,

Khalid