5 Replies Latest reply: Mar 26, 2014 6:16 PM by John Wayne RSS

    Help me out to understand the below script with IterNo()

      Hi

      This script I have found in a example application. Though it works per my requirement I want to know how does this script work so that in future if any changes are required then I can do it myself instead of depending on some one to proceed further.

      Can some one help me out to understand the below script as I could get enough details from the tutorial regarding iterno(). So I could understand what exactly this script(Bold letters!) does.

       


      Data:
      LOAD A1,
      A2,
      A3,
      A4,
      A5,
      [1],
      Datee,
      StartDate,
      EndDate
      FROM
      C:\Sample5.xlsx
      (ooxml, embedded labels, table is Sheet1);
      Calendar:
      LOAD DISTINCT
      DATE(StartDate+IterNo()-1) AS date
      RESIDENT Data
      WHILE StartDate+IterNo()-1<=EndDate;


      Thanks in advance!

        • Help me out to understand the below script with IterNo()

          Hi,

          This script will create a date for each day between StartDate and EndDate, and that for each line of the Data table.

          • Help me out to understand the below script with IterNo()

            Ok, more details :

            The while clause will keep on loading a record while the condition is false. In your example, the first line of table Data will be loaded, IterNo (for iteration) will be 1, so StartDate-IterNo+1 will be start date, and as this is below EndDate, this first record will be loaded a second time, with IterNo = 2, and so on until EndDate. The second record of table date will be processed in the same way.

            To better understand this try a simple script like :

             

            Data:
            load * inline [
            StartDate, EndDate, id
            1, 5, a
            3, 8, b
            2, 4, c
            12, 14, d
            ];

            Calendar:
            LOAD DISTINCT (StartDate+IterNo()-1) AS date, id as id2, IterNo() as IterNo, rowNo() as rowNo
            RESIDENT Data
            WHILE StartDate+IterNo()-1<=EndDate;

            • Help me out to understand the below script with IterNo()
              bandariarun

              Hi Attitude ,

              This function is only meaningful if used together with a while clause. IterNo( ) returns an integer indicating for which time one single record is evaluated in a load statement with a while clause. The first iteration has number 1.

              For Example Your Fields Havng the example data:

              StartDate EndDate

              1/1/2011 3/1/2011

              2/1/2011 4/1/2011

              3/1/2011 4/1/2011

              for the firat iteration ( loading first row), the ItrNo() returns 1, for the second row itrNo() returns 2.. and so on..

              so, DATE(StartDate+IterNo()-1) AS date

              means DATE(StartDate+1-1) AS date --> next date.. finally the reulatant table like this...

              StartDate EndDate DATE(StartDate+1-1) <= EndDate Date

              1/1/2011 3/1/2011 DATE(1/1/2011 +1-1) 1/1/2011 <=3/1/2011 true 1/1/2011

              2/1/2011 4/1/2011 DATE(2/1/2011 +2-1) 3/1/2011<=4/1/2011 true 3/1/2011

              3/1/2011 4/1/2011 DATE(3/1/2011 +3-1) 5/1/2011<= 4/1/2011 false -

               

              • Re: Help me out to understand the below script with IterNo()

                Hi,

                 

                Replying late to this post. IterNo gets the number of iterations performed so far.

                By this def, it makes sense that it can only be used with some loop.

                 

                ex. AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate)

                 

                We can use the above logic to generate date ranges of size one day starting

                from vMinDate to vMaxDate (variables). Btw, QV stores dates as number of days.

                 

                It seems to me that QV stores the number of iterations somewhere and gets
                that value from IterNo().

                 

                So, the above loop is like this in any programming language -

                 

                int count = 0;// like iterNo

                int vMinDate = 11; // say 11th day from Jan 1.

                int vMaxDate = 15

                 

                while(  (vMinDate + count) < vMaxDate )

                {

                    starts = vMinDate

                    ends = vMinDate + 1

                    print(starts, ends);

                    count = count + 1;

                }

                 

                Output -

                 

                starts, ends

                11,12

                12,13,

                13,14

                14,15

                 

                HTH