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

    Calculate invoice discount in script

    Guillermo Lo-Kioeng-Shioe

      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.

       

      Thanks in advance.

        • Re: Calculate invoice discount in script
          Gysbert Wassenaar

          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
              Guillermo Lo-Kioeng-Shioe

              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.

               

              Hope this gives you more information.

               

              Thanks in advance.

                • Re: Calculate invoice discount in script
                  Jonathan Dienst

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

                   

                  Map_Korting:

                  Mapping LOAD DebID, Deb.Korting

                  Resident Debiteuren;

                   

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

                   

                  Omzet:

                  LOAD *,

                  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;

                  LOAD ...

                      ...

                      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
                      Guillermo Lo-Kioeng-Shioe

                      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:

                       

                      Field not found - <DebID>

                      Artikelbestand:

                      LOAD HoofdgroepID,

                       

                      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:

                      LOAD *,

                      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;

                       

                      LOAD

                      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
                          Gysbert Wassenaar

                          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
                              Guillermo Lo-Kioeng-Shioe

                              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
                              Jonathan Dienst

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

                               

                              As Gysbert said, the Omzet load should use the additions I gave you, added to the existing load statement.

                               

                              The first Omzet load statement is a preceding load and is dependent on the following load statement. The second load should be the same as the existing load with the single addition of the applymap line. The ellipses ... are spaceholders for the existing code.