5 Replies Latest reply: Mar 19, 2014 3:42 PM by Staffan Johansson

# Map multiple bandings in a single table

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:
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

• ###### Re: Map multiple bandings in a single table

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'

• ###### Re: Map multiple bandings in a single table

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

• ###### Re: Re: Map multiple bandings in a single table

Here is another option:

data:

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.

• ###### Re: Map multiple bandings in a single table

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:
, 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.

• ###### Re: Map multiple bandings in a single table

Hi,

and a Intervallmatch.