topic Re: Value Mapping in QlikView

Value Mapping

Anonymous — Fri, 26 Jun 2015 12:56:50 GMT

Hi,

I don't know how to achive the following:

if Value: 100% --> 1,0

if Value: 105% --> 1,5

if Value: 90% --> 0,5

where all above 105% should yield 1,5 and all below 90% should yield 0,5

Between min and max values there should be a linear correlation. So i think i need some kind of map here...

Any suggestions?

Re: Value Mapping

sunny_talwar — Fri, 26 Jun 2015 13:00:10 GMT

Why not use a simple if statement?

If(Value < 0.9, 0.5, If(Value > 1.05, 1.5, 1)) as NewValue

Re: Value Mapping

marcus_sommer — Fri, 26 Jun 2015 16:09:19 GMT

If you have only two conditions the suggestion from sunindia is logical and simple. If you have more conditions and/or your load or gui-calculation is quite slow then it could be useful to implement a kind of lookup like these one:

pick(match(num(ceil(1.07, 0.05), '#.00', '.', ','), 0.00, ..., 1.10, .., 1.20), 0.00, ..., 0.5, ..., 1, 1.5)

- Marcus

Re: Value Mapping

Anonymous — Sun, 28 Jun 2015 17:04:20 GMT

Thanks for you help sunindia, but im afraid this isnt exactly what i need.

If I do it like this, there are only three possible Values für 'NewVaule' (0.5,1 and 1.5).

If 'Value' is 1.04, 'NewValue' should be something like 1.4 and so on...

Re: Value Mapping

maxgro — Sun, 28 Jun 2015 17:28:26 GMT

if(Exp1<=1, rangemax(0.5 + 5*(Exp1-0.9), 0.5),

if(Exp1>1, rangemin(1 + 10*(Exp1-1.0), 1.5),

'Error'))

SET ThousandSep=',';

SET DecimalSep='.';

Table1:

LOAD Dim1, Exp1/100 as Exp1 Inline [

Dim1, Exp1

C, 50

C1,90

C2,92

C3,95

C4,96

C5,98

D, 100

D1,101

D2,102

D3,103

D4,104

D5, 105

D6, 150

] ;

Re: Value Mapping

MarcoWedel — Sun, 28 Jun 2015 20:19:15 GMT

Hi,

another solution could be:

=RangeMin(1.5,RangeMax(0.5,(Value-1)*5*(2+(Value<1))+1))

hope this helps

regards

Marco

Re: Value Mapping

Anonymous — Mon, 29 Jun 2015 07:48:38 GMT

Hi Massimo,

thx, that did the trick!

Regards,

Phil