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

[joris_lansdaal]

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

[Anonymous · Accepted solution]

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]

Hi adhpco16,

Thank you for support!

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

[Anonymous]

You are welcome 😉