4 Replies Latest reply: Dec 8, 2017 7:48 AM by RAVI SHINGE RSS

    How to use For loop

    RAVI SHINGE

      Hello Guys,

       

      I have around 200 different projects with unique ids which has :-

      a. Start date

      b. End date

      My tables looks like this as shown below :-

      ID ProjectStart date(MMDDYYYY)End Date(MMDDYYYY)
      1202/02/201520/06/2019
      3430/07/201604/05/2018
      5626/04/201606/07/2019

       

      Using FOR loop I want to calculate

      a. Difference between start date and end date in Days and in Months

       

      Thanks In Advance

      ravi

        • Re: How to use For loop
          Massimo Grossi

          I think you can calculate 2 new fields in the same table without a for loop

           

          T:

          load * inline [

          ID, StartDate, EndDate

          12, 02/02/2015, 20/06/2019

          34, 30/07/2016, 04/05/2018

          56, 26/04/2016, 06/07/2019

          100, 8/12/2017, 9/12/2017

          100, 8/12/2017, 8/12/2017

          100, 8/12/2017, 8/1/2018

          ];

           

          F:

          LOAD

               *,

               EndDate - StartDate as DaysDiff,

               Num(((year(EndDate) * 12) + month(EndDate)) - (((year(StartDate) * 12) + month(StartDate))) + 1) as MonthsDiff

          Resident T;

           

          DROP Table T;





          If you want a for loop

           

          T:

          load * inline [

          ID, StartDate, EndDate

          12, 02/02/2015, 20/06/2019

          34, 30/07/2016, 04/05/2018

          56, 26/04/2016, 06/07/2019

          100, 8/12/2017, 9/12/2017

          100, 8/12/2017, 8/12/2017

          100, 8/12/2017, 8/1/2018

          ];

           

          for i=0 to NoOfRows('T') -1

               LET vStart = Peek('StartDate', $(i), T);

               LET vEnd = Peek('EndDate', $(i), T);

               LET vDaysDiff = '$(vEnd)' - '$(vStart)';

               trace i=$(i);

               trace vStart=$(vStart);

               trace vEnd=$(vEnd);

               trace vDaysDiff=$(vDaysDiff);

          NEXT

            • Re: How to use For loop
              RAVI SHINGE

              Thanks for your reply !

               

              As I have too many projects nearly 250 and above , So i would not want to to load inline . I have separate field for ProjectId, Startdate, End Date . So I would want to use field rather than going for load in line .

              Also want to calculate no. of months between start date and end date .

              Using For loop only .

               

              And also is this script for qlikview or qliksense

              Kindly help me with this .

                • Re: How to use For loop
                  Massimo Grossi

                  I used inline to make some test data, you can replace my inline data with your table data; maybe you have to change the names of the fields.

                   

                  This works for QlikView and Qlik Sense, the syntax of the script is the same (almost the same); change the table name and field names with yours.

                  The differnce is this script calculates variables, the script I posted at the beginning of previous post calculates 2 new fields you can use in charts.

                   

                  for i=0 to NoOfRows('T') -1

                       LET vStart = Peek('StartDate', $(i), T);

                       LET vEnd = Peek('EndDate', $(i), T);

                       LET vDaysDiff = '$(vEnd)' - '$(vStart)';

                       trace i=$(i);

                       trace vStart=$(vStart);

                       trace vEnd=$(vEnd);

                       trace vDaysDiff=$(vDaysDiff);

                  NEXT

                    • Re: How to use For loop
                      RAVI SHINGE

                      Thanks you for reply !

                      Now in case i use Fields in my inline load ..what will be my

                       

                      "for i=0 to NoOfRows('T') -1     ?




                      As of now , I am doing something like this

                      [Line]:

                      load

                      IDprojects,

                      "StartDate",

                      EndDate,

                       

                      For i=0 to NoOfRows('Line') -1

                          LET vStart = Peek('StartDate', $(i), T);

                          LET vEnd = Peek('EndDate', $(i), T);

                          LET vDaysDiff = '$(vEnd)' - '$(vStart)';

                          trace i=$(i);

                          trace vStart=$(vStart);

                          trace vEnd=$(vEnd);

                          trace vDaysDiff=$(vDaysDiff);

                      NEXT

                       

                       

                      At the end  i also want to

                      calculate the Sum of (difference in days of all projects). Can u plz give entire script if possible .

                       

                      Thanks In advance