Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table with a lot of numeric value fields, all of which need to be banded up into ranges (string). I have had a look at IntervalMatch but I'm not certain it is what I want - at least I have not seen a useful example of my scenario.
As an example:
data:
load * inline [
A,B,C,D,E
1,3,4,5,2
10,3,20,3,2
39,2,49,2,10];
I now want to apply a banding to each of these fields.
For A I want the banding to be:
If >0 <= 5 then Low
If >5 <=20 then Med
If >20 then High
For the others:
IF >0 <=10 Low
IF >10 High
This is a simple example of what I'm trying to do, but to put it in context I have a table with about 50 fields I need to band and don't want to have to write IF THEN ELSE code for each one.
Cheers,
GPC
The class function should be what you're looking for:
class(expression, interval [ , label [ , offset ]])
Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.
Examples:
class( var,10 ) with var = 23 returns '20<=x<30'
class( var,5,'value' ) with var = 23 returns '20<= value <25'
class( var,10,'x',5 ) with var = 23 returns '15<=x<25'
Hi Nicole,
The Class is useful in some situations, but here my bandings are not a set distance apart, e.g. not always 10 between lower and upper bounds.
GPC
Several options can be used. Mapping tables is one:
MapA:
mapping load RecNo()-1 as K, 'Low' as V
AutoGenerate 6;
mapping load RecNo()+5 as K, 'Med' as V
AutoGenerate 15;
MapOther:
mapping load RecNo()-1 as K, 'Low' as V
autogenerate 11;
data:
load *
, ApplyMap('MapA',A,'High') as LevelA
, ApplyMap('MapOther',B,'High') as LevelB
, ApplyMap('MapOther',C,'High') as LevelC
, ApplyMap('MapOther',D,'High') as LevelD
, ApplyMap('MapOther',E,'High') as LevelE
inline [
A,B,C,D,E
1,3,4,5,2
10,3,20,3,2
39,2,49,2,10];
See attached example.
Here is another option:
data:
load * inline [
A,B,C,D,E
1,3,4,5,2
10,3,20,3,2
39,2,49,2,10];
left join (data)
load distinct A, if(A>20,'High',if(A>5,'Medium','Low')) as BandingA resident data;
for each Value in 'B','C','D','E'
left join (data)
load distinct $(Value), if($(Value)>10,'High','Low') as Banding$(Value) resident data;
next Value
Example file is attached.
Hi,
and a Intervallmatch.