Mapping expression output

[aetingu12]

Dear all,

I have been reading up on many different questions where people are trying to map values. But I wasn't able to find something close to what I need.

I have an expression in a pivot table that calculates the weighted average of a metric ie.

sum(Rate* Balances)/sum(Balances)

I would like to map the output of this expression to a table I have. The table is like this.



Mapping_tab:

Load * inline [

Min,Max,Group

0,0.01,1

0.01,0.03,2

0.03,0.04,3

0.04,0.08,4

0.08,0.13,5

0.13,0.18,6

0.18,0.23,7

0.23,0.28,8

]

So if the expression result is between 0.13 and 0.18 then I want the expression to output 6.

Any help is more than appreciated.

Regards

[Zhandos_Shotan]

Hi!

If expression  sum(Rate* Balances)/sum(Balances) is possible to calculate in script, you can use intervalmatch function with your map table. Pls ref to manuals:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalM...

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/matching-intervals-to-dis...

Else, in expression, you need to use something like:

If(a>x1 and a<y1, 1,

    if(a>x2 and a<y2, 2,

         if(a>x2 and a<y2, 3,

              if(...))))

But it will be very slow

[marcus_sommer · Accepted solution]

You could use an approach like this one:

Mapping_tab:
Load Min + ((iterno() - 1) / 100) as Result, Group while Min + ((iterno() - 1) / 100) <= Max;
Load * inline [
Min,Max,Group
0,0.01,1
0.01,0.03,2
0.03,0.04,3
0.04,0.08,4
0.08,0.13,5
0.13,0.18,6
0.18,0.23,7
0.23,0.28,8
];

to extend the mapping-table to discrete values and within the expression you used a pick(match()) with adhoc-variables to return the Group, for example with:

pick(match(floor(sum(Rate* Balances)/sum(Balances), 0.01), $(=concat(Result, ',')), $(=concat(Group, ',')))

- Marcus