2 Replies Latest reply: Sep 23, 2016 8:52 AM by ANDY SALLEE RSS

    For Next/Do While loop

    ANDY SALLEE

      Hello all,

      I am struggling with creating a table with all dates listed from another table. The original has a project ID, Start date, end date and projection amount. Because the start and end dates can span across months I am looking to be able to get projection for a given month/Week etc. based upon total projection amount divided by days of project.

      Example:

      ProjectId, StartDate, EndDate, Projection

      1, 8/29/2016, 9/2/2016, 150,000

      2, 9/1/2016, 9/05/2016, 100,000

       

      Each project has 5days of work so daily projection would be 15,000 & 10,000 respectively.

      Would like table to be created as follows:

       

      ProjectId, Date, Projection

      1, 08/29/2016, 15000

      1, 08/30/2016, 15000

      1, 08/31/2016, 15000

      1, 09/01/2016, 15000

      1, 09/02/2016, 15000

      2, 09/01/2016, 10000

      2, 09/02/2016, 10000

      2, 09/03/2016, 10000

      2, 09/04/2016, 10000

      2, 09/05/2016, 10000

       

      Then would be able to sum projection by month

       

      Example August would be 45,000 for both projects and September would be 205,000 for both projects and total would be 250,000 across both months. Below is code I have to start of course not working. Would appreciate if see better way to do this or what I have incorrect?

       

      Thanks

      //Create table for daily projection volumes

      Let vJobStartDate = Today();
      Let vJobEndDate = Today();
      Let vServiceDays = 0;


      Let vNoOfRows = NoOfRows('ProjectMaster');   
      //Get the values from each row and create a daily projection table
      For i=1 to $(vNoOfRows)-1
      set vProjectId = Peek('ProjectId',$(i),'ProjectMaster');
      set vJobStartDate = Peek('StartDate',$(i),'ProjectMaster');
      set vJobEndDate = Peek('EndDate',$(i),'ProjectMaster');
      set vServiceDays = Peek('EndDate',$(i),'ProjectMaster') - Peek('StartDate',$(i),'ProjectMaster');
      set vDailyProjection = Peek('ProjectionTest',$(i),'ProjectMaster')/$(vServiceDays);

        // This script loops through each ProjectId and creates a table with the date and daily amounts for each date

      Set a=1;
      Do while a < vServiceDays

      ProjectionTable:
      Load if(isnull($(vProjectId)),'NULL'&$(i),$(vProjectId)) as ProjectId,
      if(isnull($(vJobStartDate)),'NULL'&$(i),$(vJobStartDate)) as JobProjectionDate,
        if(isnull($(vDailyProjection)),'NULL'&$(i),$(vDailyProjection))as DailyProjection;
      Let a=a+1;
      Let vJobStartDate =vJobStartDate+1;
      Loop


      Next i;

        • Re: For Next/Do While loop
          Stefan Wühl

          Use a WHILE clause to your LOAD statement:

           

           

           

          SET DateFormat = 'M/D/YYYY';

          Set DecimalSep = '.';

          Set ThousandSep = ',';

           

           

          LOAD *, Date(StartDate +IterNo()-1)  as ProjectDate, Projection / (2*(EndDate-StartDate+1)) as ProjectionNew INLINE [

          ProjectId, StartDate, EndDate, Projection

          1, 8/29/2016, 9/2/2016, "150,000"

          2, 9/1/2016, 9/05/2016, "100,000"

          ]

          WHILE StartDate +IterNo()-1 <= EndDate;

           

          I am not exactely sure about your Projection calculation, adapt as needed

            • Re: For Next/Do While loop
              ANDY SALLEE

              Thank you Stefan, I was able to expand on this concept and get to work as I wanted from your suggestion.

               

              Here was my final code for others to perhaps gain from as well.

               

              //****from Henric post combined with Stefan lead @

              //  https://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

               

               

              TempProjectionTable:

              Load

                ProjectId,

                StartDate,

                  EndDate,

                Projection

              Resident ProjectMaster

              // Exclude the Company, Admin & Training Clients

              where ClientId <> 168 and

                ClientId <> 214 and

                  ClientId <> 237;

               

               

              ProjectionTable:

              Load

                ProjectId,

                  Num(StartDate + IterNo() -1) as JobProjectionDate,

                  Round(Projection/(Num(EndDate)-Num(StartDate)+1),0.00001) as ProjectionAmount

              Resident TempProjectionTable

              While IterNo() <= EndDate - StartDate +1

              Order by StartDate asc;

              Drop Table TempProjectionTable;