Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

joris_lansdaal
Contributor

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

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

bonus base.PNG

1 Solution

Accepted Solutions
adhpco16
New Contributor III

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

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';

3 Replies
adhpco16
New Contributor III

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

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';

joris_lansdaal
Contributor

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

Hi adhpco16,

Thank you for support!

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

adhpco16
New Contributor III

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

You are welcome ;-)

Community Browser