Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

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

5 Replies
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'

Not applicable
Author

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.


talk is cheap, supply exceeds demand
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
Partner - Master
Partner - Master

Hi,

and a Intervallmatch.