topic Help: Use a value in a Field to determine Field to use in QlikView

Help: Use a value in a Field to determine Field to use

joris_lansdaal — Thu, 30 Jun 2016 09:31:37 GMT

Hi all,

Is it possible within the script to use the field Bonus_Base to determine which Field to use to calculate an amount ?

e.g 0,02*160 for line 1, 3,7%*2455,883 for line 6 etc.

Or do I have to use an if-statement? I don't hope so because there will be like 8 different Bonus_Base values.

Can anyone point me in de right direction?

Thanks Joris

Re: Help: Use a value in a Field to determine Field to use

Anonymous — Thu, 30 Jun 2016 11:29:21 GMT

Hello Joris,

In the script you can load your excel with a where condition and then you can make your calculation into a new field and then concatenate (QV will automaticly do this also without the word concatenate) the table so that you have all your fields.

Maybe try like this in the script:

Table:

LOAD %Artnr|Datum|Hoofd,

Bonus_%,

Bonus_Abs,

Bonus_Base,

Volume,

NNOI,

NOI,

Bonus_Abs*Volume as Amount

FROM

(ooxml, embedded labels, table is Tabelle1)

where Bonus_Base = 'Volume';

Concatenate

LOAD %Artnr|Datum|Hoofd,

Bonus_%,

Bonus_Abs,

Bonus_Base,

Volume,

NNOI,

NOI,

Bonus_%*NNOI as Amount

FROM

(ooxml, embedded labels, table is Tabelle1)

where Bonus_Base = 'NNOI';

Re: Help: Use a value in a Field to determine Field to use

joris_lansdaal — Sat, 02 Jul 2016 06:52:27 GMT

Hi adhpco16,

Thank you for support!

I used your solution together with a loop for all the Bonus_% calculations.

Re: Help: Use a value in a Field to determine Field to use

Anonymous — Tue, 05 Jul 2016 06:25:57 GMT

You are welcome 😉