9 Replies Latest reply: Feb 25, 2014 11:59 AM by Srikanth P RSS

    Lighten up the script

    Antonin Ramet

      Hi everyone,

       

      I'm resquesting your help for one little thing. I'm trying to lighten a part of my script.

      The idea is : if you don't pay for the bill until a certain time, you've got interest.

      According to the month and year, the time and the interest are not the same.

       

      Before 01/07/2011 = 35 days / 8%

      01/07/2011 - 30/12/2011 = 30 days /: 8,25%
      2012 = 30 days : 8%

       

      After, when I load my data, I want to calculate the interest.

      So I've got : LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

                        Delay,

                        Sum_bill.

       

      But when I want to calculate the interest, I've got this ugly line :

       

      if(([Delay]>30) and ((([Sum_bill.]*if(((num(month([Date]))<7)and (num(year([Date]))=2012)),0.0825,if(((num(month([Date]))<7)and (num(year([Date]))=2011)),0.08,if((num(month([Date]))>=7) and (num(year([Date]))=2012),0.08,if(((num(month([Date]))>=7)and (num(year([Date]))=2011)),0.0825)))))*(([Delay]-30)/365))>5),(([Sum_bill.]*if(((num(month([Date]))<7)and (num(year([Date]))=2012)),0.0825,if(((num(month([Date]))<7)and (num(year([Date]))=2011)),0.08,if((num(month([Date]))>=7) and (num(year([Date]))=2012),0.08,if(((num(month([Date]))>=7)and (num(year([Date]))=2011)),0.0825)))))*(([Delay]-30)/365))) as interets.

       

      Do you have any ideas to lighten up my script ?

       

      Thanks in advance !

        • Re: Lighten up the script

          Antonin,

           

          I would create another table that stores these numbers. Create a calendar or sth like that.

          Use intervalmatch (see excellent post by HIC on that topic) so that each date has a Delay and a Interest Rate. You may also compute an interest rate by day, so that you just have to sum(Amount*IR by day)

           

          Afterwards, it should be easier to sum up the interest rate.

           

          Fabrice

          • Re: Lighten up the script
            A Harish

            hi antonin , my advice is to use calender object

            • Re: Lighten up the script
              Antonin Ramet

              Thank you for the answers, but now I'm struggling with the Intervalmatch().

               

              The structure is the next :

               

              Mandat :

              LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

              ...

              from [.../Délais 2012.xls]


              LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

              ...

              from [.../Délais 2013.xls]


              Interval :

              LOAD * INLINE [

              date_from, date_to, taux_journalier, delai

              01/01/2008, 31/12/2008, 0.00016411, 45

              01/01/2009, 30/06/2009, 0.000246575, 40

              01/07/2009, 31/12/2009, 0.000219178, 40

              01/01/2010, 30/06/2010, 0.000219178, 35

              01/07/2010, 31/12/2010, 0.000219178, 30

              01/01/2011, 30/06/2011, 0.000219178, 30

              01/07/2011, 31/12/2011, 0.000226027, 30

              01/01/2012, 30/12/2012, 0.000219178, 30

              01/01/2013, 30/12/2013, 0.000212329, 30

              ];

               

              Intervalmatch:

              IntervalMatch(Date_reception)

              load distinct date_from, date_to, taux_journalier,delai Resident Interval;

               

               

              But it never creates Intervalmatch, and nothing is linked. What did I miss ?

                • Re: Lighten up the script
                  Karl Pover

                  Hi Antonin,

                   

                  I know that this isn't the real script, but make sure the field names match.  The date_reception will not match with Date_reception. 

                   

                  Also make sure that the dates in the Inline are being interpreted as dates and not strings.  In QlikView do a num(date_from) to see if it returns a number or a null value in the case it is a string.  If it is a string load it again as a resident load and use the date#() function to convert the string to date format.

                   

                  Karl

                    • Re: Lighten up the script
                      Antonin Ramet

                      Well, I edited my post, you scared me. I thought that it was just a mistake, and I didn't pay attention to it, but no, in my script, I used the same name everywhere.

                       

                      And it gaves me a number when I do a num(date_from), : for the 01/01/2009 => 39814. So Qlikview doesn't interprete it as a date, isn't it ?

                        • Re: Lighten up the script
                          Karl Pover

                          Try the following script:

                           

                          Mandat :

                          LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

                          ...

                          from [.../Délais 2012.xls]


                          LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

                          ...

                          from [.../Délais 2013.xls]


                          Interval :

                          LOAD * INLINE [

                          date_from, date_to, taux_journalier, delai

                          01/01/2008, 31/12/2008, 0.00016411, 45

                          01/01/2009, 30/06/2009, 0.000246575, 40

                          01/07/2009, 31/12/2009, 0.000219178, 40

                          01/01/2010, 30/06/2010, 0.000219178, 35

                          01/07/2010, 31/12/2010, 0.000219178, 30

                          01/01/2011, 30/06/2011, 0.000219178, 30

                          01/07/2011, 31/12/2011, 0.000226027, 30

                          01/01/2012, 30/12/2012, 0.000219178, 30

                          01/01/2013, 30/12/2013, 0.000212329, 30

                          ];

                           

                          Intervalmatch:

                          IntervalMatch(Date_reception)

                          load distinct date_from, date_to  Resident Interval;

                          • Re: Re: Lighten up the script
                            Srikanth P

                            Hi Antonin, please find the attached qvw for reference.

                        • Re: Lighten up the script

                          Antonin,

                           

                          As said in the help file:

                          loadstatement or selectstatement must result in a two-column table,

                           

                          Taux_Journalier and Delai are too many in the syntax. That is why.

                          Karl's syntax should work.

                           

                          Fabrice

                        • Re: Lighten up the script
                          Antonin Ramet

                          Thanks a lot ! I wasn't paying attention to that sentence. Shame on me !