Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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')