Map multiple bandings in a single table

[#1]

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

[Nicole-Smith]

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'

[#3]

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

[Gysbert_Wassenaar]

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.

[Nicole-Smith]

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.

[stabben23]

Hi,

and a Intervallmatch.