1 Reply Latest reply: Oct 31, 2014 12:45 PM by Jonathan Poole RSS

    Why do I need a RowNo() function and use Min/MaxDate Variables in the MasterCalender?

      Hello,

       

      I am inheriting a Master Calender script but I do not understand the following part:

       

      My questions are in bold in the script where I do not understand. Could anyone help me please understand it

      -----------------------------------------------------------------------------

      MinMax:

      LOAD

          Min(Date) AS MinDate,

          Max(Date) AS MaxDate

      RESIDENT Facts;

       

      //Variables

      LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMax'));

      LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

      Why do we need variables to hold the Min Date and Max Date? From what I can see they will always only hold one value so why do we need to create the variables, can't we just use the Min(Date) and Max(Date)?

       

      // Temporary calender

      TempCal:

      LOAD

          DATE($(vMinDate)+ ROWNO()-1) AS TempDate

      Autogenerate

          $(vMaxDate) - $(vMinDate)+ 1;

       

      Why do we use ROWNO() function here? Does it identify each row a date is in the TempCal table? And why do we put a -1 after it?


      I am not clear on these things

        • Re: Why do I need a RowNo() function and use Min/MaxDate Variables in the MasterCalender?
          Jonathan Poole

          Why do we need variables to hold the Min Date and Max Date? From what I can see they will always only hold one value so why do we need to create the variables, can't we just use the Min(Date) and Max(Date)?


          From what i can see in the script alone, setting the max and min as variables does this

           

          -> Define variables for the application (they are used in your script but could also be used in your UI).  In this case the LET is setting these 2 variables to constants that represent the min and max date.  

           

          -> The variables are NOT needed for the script but the script is cleaner and easier to manage because of them .  In the 2nd load for example it autogenerates a table with the number of days equal to max-min.  You could as easily have used the peek() expression in lieu of the variables.  BUT the same expression for max date would have to be duplicated to set the vToday function. So if you don't set variables to manage the expression in one place you would have to manage in 2 places.

           

          Why do we use ROWNO() function here? Does it identify each row a date is in the TempCal table? And why do we put a -1 after it?


          This table is auto generating a number of records equal to the number of days between min and max date. For each row  (first row = 1)  the row number will add one to the date.  Dates are whole numbers in Qlik so Date+1 = the day after.  Reading this script, the first row needs to be the mindate so adding rowno (=1) to that will be the day after. So subtract 1 so that the first row is actually = vMinDate.  The next row (=2) is the day after the min and so on.  An extra row is added in the autogenerate (it says +1) to make sure the vMaxDate is included .