topic Re: Mapping expression output in QlikView

Mapping expression output

aetingu12 — Sat, 16 Nov 2024 04:51:36 GMT

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

Re: Mapping expression output

Zhandos_Shotan — Thu, 03 Jan 2019 11:58:12 GMT

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/IntervalMatch.htm

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

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

Re: Mapping expression output

marcus_sommer — Thu, 03 Jan 2019 12:08:29 GMT

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