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

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

Thu, 25 Mar 2010 00:33:57 GMT

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?

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

Oleg_Troyansky — Thu, 25 Mar 2010 02:29:48 GMT

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.

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

Anonymous — Thu, 25 Mar 2010 04:49:19 GMT

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

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

Thu, 25 Mar 2010 16:49:12 GMT

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,

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

johnw — Fri, 26 Mar 2010 00:58:10 GMT

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

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

Mon, 29 Mar 2010 21:12:46 GMT

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,

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

johnw — Mon, 29 Mar 2010 21:18:44 GMT

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')

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

Mon, 29 Mar 2010 21:25:47 GMT

Thanks for your help. It worked!

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

Thu, 17 Jun 2010 11:04:32 GMT

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

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

Thu, 17 Jun 2010 11:05:33 GMT

Hi There,

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

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

johnw — Thu, 17 Jun 2010 18:49:36 GMT

Well, in the other thread where you posted this, it sounded like someone wanted you to discover the answer for yourself. Here you don't say this, so I'm free to post the five or six solutions I have, depending on how you count them. I suggest using the exists() solution. It seems simplest.

Script solutions:

[City List]:
LOAD * INLINE [
City List
London
New York
Maxico
];
[City List 2]:
MAPPING LOAD
"City List"
,'in list' as "In List? 2"
RESIDENT [City List]
;

SET InCityList = 'if(match($1';
FOR I = 1 TO noofrows('City List')
LET InCityList = InCityList & ',' & chr(39) & fieldvalue('City List',$(I)) & chr(39);
NEXT
LET InCityList = InCityList & '),' & chr(39) & 'in list' & chr(39) & ',' & chr(39) & 'not in list' & chr(39) & ')';

[Data]:
LOAD
"City"
,if(exists("City List","City"),'in list','not in list') as "In List? 1"
,applymap('City List 2',"City",'not in list') as "In List? 2"
,$(InCityList("City")) as "In List? 3"
INLINE [
City
London
New York
Maxico
San Franciso
Venice
];
LEFT JOIN ([Data])
LOAD
"City List" as "City"
,'in list' as "In List? 4 Temp"
RESIDENT [City List]
;
LEFT JOIN ([Data])
LOAD
"City"
,if(len("In List? 4 Temp"),"In List? 4 Temp",'not in list') as "In List? 4"
RESIDENT [Data]
;
DROP FIELD "In List? 4 Temp";

Chart solutions:

Expression = $(InCityList("City"))
Expression = if(match("City",$(=chr(39)&concat("City List",chr(39)&','&chr(39))&chr(39))),'in list','not in list')

Edit: Another chart solution:

Expression = if(count({<"City"=P("City List")>} "City"),'in list','not in list')