Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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';
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';
Hi adhpco16,
Thank you for support!
I used your solution together with a loop for all the Bonus_% calculations.
You are welcome 😉