Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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'

Not applicable

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

MVP & Luminary
MVP & Luminary

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:

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

Re: Re: Map multiple bandings in a single table

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
Honored Contributor

Re: Map multiple bandings in a single table

Hi,

and a Intervallmatch.

Community Browser