topic Number Ranges within a slider in QlikView

Number Ranges within a slider

Anonymous — Thu, 15 Apr 2010 15:42:50 GMT

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

Number Ranges within a slider

Thu, 15 Apr 2010 15:48:12 GMT

Hello,

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

Hth,
Thilo

Number Ranges within a slider

Anonymous — Thu, 15 Apr 2010 16:40:45 GMT

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

Number Ranges within a slider

Thu, 15 Apr 2010 16:49:43 GMT

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

Number Ranges within a slider

Thu, 15 Apr 2010 16:57:28 GMT

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.

Number Ranges within a slider

Anonymous — Thu, 15 Apr 2010 17:12:28 GMT

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

Number Ranges within a slider

johnw — Fri, 16 Apr 2010 00:43:08 GMT

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.

Number Ranges within a slider

Anonymous — Fri, 16 Apr 2010 14:04:12 GMT

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