8 Replies Latest reply: Oct 27, 2015 9:55 AM by Guillermo Lo-Kioeng-Shioe

# Calculate invoice discount in script

The question i have is quite easy.

So i have a few debtors who have get a discount in %. This is determined in our ''Debtors" table, however Qlikview does not take that discount into consideration when it calculates the sales which is found in my revenues table.

So the information is in different tables, also the discount is only on products that have taxes or not, which is in the same table of where i calculate the amount of revenue. So that shouldn't be a problem.

So basically my question is: how can i calculate the amount minus discount from two different tables. where i also need an IF function where it only calculates if it has taxes or not.

• ###### Re: Calculate invoice discount in script

Perhaps you can use a mapping table and the applymap function. Or the LookUp function. If that's not possible you may have to join tables so that all the fields necessary for the calculation exist in one table. If you need more help then please post an example qlikview or qlik sense document that illustrates the problem.

• ###### Re: Calculate invoice discount in script

Hi,

So i added the two tables that i mentioned in my post. As you can see in the 'Omzet' Tab i have a formula where i calculate the revenue including taxes. (the 1.06 and 1.21 part) However i also need to calculate the discount which is in the next tab 'Debiteuren' under 'Deb.Korting'.

I think the formula needs to be:

IF (InvLi.BTW='L',InvLi.Bedrag*1.06*Deb.Korting,

IF (InvLi.BTW='H',InvLi.Bedrag*1.21*Deb.Korting,InvLi.Bedrag)) as InvLi.InclBTW,

However the Deb.Korting is in the other table/sheet.

Both tables are linked through Deb ID so i could calculate it in the table but i rather have it in the script.

• ###### Re: Calculate invoice discount in script

Get Deb.Korting using a mapping. First load Table Debiteuren before Omzet. After the load for Debiteuren:

Map_Korting:

Resident Debiteuren;

Then modify the load for Omzet (only new lines shown):

Omzet:

IF (InvLi.BTW='L', InvLi.Bedrag * 1.06 * Omzet.Deb.Korting,

IF (InvLi.BTW='H', InvLi.Bedrag * 1.21 * Omzet.Deb.Korting, InvLi.Bedrag)) as InvLi.InclBTW;

...

ApplyMap('Map_Korting', DebID, 0) As Omzet.Deb.Korting,

...

;

If you like, you can drop the Omzet.Deb.Korting field after the load

DROP FIELD Omzet.Deb.Korting;

• ###### Re: Calculate invoice discount in script

Hi Jonathan,

This seems to work but one strange thing happens.

When i put an exit script after the omzet load it works fine however when i remove the exit script and let it continue i get this error:

Artikelbestand:

Maybe i addded the new lines not in the right place?

This is how it looks:

FROM

[R:\Qlikview\DBFiles\InvLi120.Qvd]

(qvd)

where OBArtID <>99999999;

Omzet:

IF (InvLi.BTW='L', InvLi.Bedrag * 1.06 * Omzet.Deb.Korting,

IF (InvLi.BTW='H', InvLi.Bedrag * 1.21 * Omzet.Deb.Korting, InvLi.Bedrag)) as InvLi.InclBTW;

ApplyMap('Map_Korting', DebID, 0) As Omzet.Deb.Korting;

Exit Script;

This works fine, however when i remove the Exit script it causes mentioned error.

• ###### Re: Calculate invoice discount in script

I think you've taken Jonathans code too literally. You have to add his code to your code, not replace it. Like he said:

Then modify the load for Omzet (only new lines shown):
• ###### Re: Calculate invoice discount in script

I have added the code to my script, i didnt replace anything.

I have tried two options so far: Adding the lines in the omzet sheet which doesnt work at all.

What happens is either QV loads the table and I can display the new mapped column, but i cant use it in a formula because QV states that it cant find the new column (cause its looking in the original omzet sheet. Where it obviously isnt located.

If I add the lines in the end of my omzet sheet it doesnt get loaded at all.

So i am doing something terribly wrong but i cant seem to find out what.

I have also tried to just load the ApplyMap('Map_Korting', DebID, 0) as Korting, directly in the omzet sheet which works but i cant use the new line in a formula, cause then it states it cant find the line 'korting' but i can display it in a table.

I have the feeling mapping table is just like VLookup. I can display the newly found information however i cant use it in the same sheet to get new information from it.

At this point it would be easier to just add the Deb.Korting in the omzet QVD and make it work like that.

• ###### Re: Calculate invoice discount in script

For the sake of space and time, I did not replicate the entire code.