Number Ranges within a slider

[Anonymous]

Hello all - although I have used Qlikview for some time, this is my first time using the forums, so please bare with me 😉

I'm doing some analysis for a loss prevention dept and I would like to see incidents based on the loss value of the incident itself. Specifically I'd like to see the values of loss put into categories or ranges as follows; <100, 100-200, 200-300, 400-500, and >500. I would then like to use a slider object for selection purposes.

I'm pretty sure I could accomplish this in the data load via transact sql using a CASE argument but was curious if there was another or possibly easier way to do this?

Thanks in advance,

Simon

[#2]

Hello,

check out the IntervalMatch statement (see Help file).
You might use an Inline Table to define the interval ranges.

Hth,
Thilo

[Anonymous]

Thanks Thilo - i was not aware of that function. I'm having trouble getting it to work with a combination of an inline load and a db load. Maybe I'm missing something - do you have any examples where you have used IntervalMatch when you are loading data from Inline and a db?

Thanks again,

Simon

[#4 · Accepted solution]

Simon,

I used this successfully:

PricePointIntervals:
LOAD * INLINE [
 PriceStart,PriceEnd,PriceInterval
 , '-0,01', "< $0"
 0, '99,99', "$0-$99"
 100, '199,99', "$100-$199"
 200, '299,99', "$200-$299"
 300, '399,99', "$300-$399"
 400, '499,99', "$400-$499"
 500, '599,99', "$500-$599"
 600, '699,99', "$600-$699"
 700, '799,99', "$700-$799"
 800, '899,99', "$800-$899"
 900, '999,99', "$900-$999"
 1000, , "> $1000"
];
MyData:
LOAD
 A,
 B,


 SellingPrice,

 D,
 E
FROM ... (I used Excel as a source, but SQL will make no difference here);
IntervalMatch(SellingPrice)
Load
 PriceStart,
 PriceEnd
resident PricePointIntervals;

hth,
Thilo

[#5]

looking at it I probably need to add that I am using German Regional settings, i.e. my decimal separator is 'comma'. That's why I quoted the values in the inline tab.

[Anonymous]

Thanks again Thilo - I was trying to use the IntervalMatch with Select instead of Load - works perfectly now 🙂

Thanks very much for the help!

Simon

[johnw]

As an alternative, for only six categories like this, I'd just write a nested if() and add it to the main load rather than building an intervalmatch():

,if(Loss<100,dual('<100',1)
,if(Loss<200,dual('100-200',100)
,if(Loss<300,dual('200-300',200)
,if(Loss<400,dual('300-400',300)
,if(Loss<500,dual('400-500',400)
,dual('500+',500)))))) as "Loss Category"

You can put the field in a slider if you want, and it'll be sorted correctly due to the underlying numeric values supplied by the dual() function. I'm not sure exactly how many categories I'd have to have before I swapped over to an intervalmatch. A dozen maybe? Twenty? Just whenever it started to look like more of a headache to maintain the nested if() than to bite the bullet and go all the way to the intervalmatch. Both work, of course, so it's probably up to personal preference.

[Anonymous]

Thanks very much John! I have already created the intervalmatch and it wasn't too much work. Always great to have alternatives for the next time though!

Thanks again,

Simon