2 Replies Latest reply: Nov 12, 2013 11:16 AM by Bill Markham RSS

    Generate data in a range

    Carlos Borau

      Hi all, I'm new to clikview so this might be a silly question... In any case here it goes:

       

      I have an Excel file with four fields: Employee, Date_start, Date_end, Number_of_days

       

      Eg:

      OriginalTable:

      Nick  1/1/2013       2/1/2013   2
      John  9/1/2013     11/1/2013   3
      Nick  19/1/2013   21/1/2013   3


      I want to create a new table containing all the dates of the corresponding range:


      FinalTable:

      Nick 1/1/2013
      Nick 2/1/2013
      John 9/1/2013
      John 10/1/2013
      John 11/1/2013
      Nick 19/1/2013
      Nick 20/1/2013
      Nick 21/1/2013


      What I have tried is to extract the number of rows of the OriginalTable and make a double loop as follows:


      FOR i=1 TO $(vnRows)

       

          LET vDateStart= num(Peek('Date_start',$(i)-1,'OriginalTable'));

          LET vDateEnd = num(Peek('Date_end',$(i)-1,'OriginalTable'));

          LET vLoop = $(vDateEnd)-$(vDateStart)+1;

         

          FOR j=1 TO $(vLoop)

              

             

              FinalTable:

              LOAD

              Peek('Employee',$(i)-1,'OriginalTable') AS Employee_final,

              Date($(vDateStart) + $(j) - 1) as Date_Final

              FROM ??????FROM WHERE?  THIS IS THE PART I'M MISSING. I TRIED CREATING AN AUXILIAR TABLE BUT GOT SOME ERRORS

       

          NEXT

       

      NEXT

       

      Thanks for your help!

        • Re: Generate data in a range
          Carlos Borau

          I finally solved it, thanks to Bill that gave me the tip (Remove the second loop and use AutoGenerate). It should look as follows:

           

          FOR i=1 TO $(vnRows)

              LET vDateStart= num(Peek('Date_start',$(i)-1,'OriginalTable'));

              LET vDateEnd = num(Peek('Date_end',$(i)-1,'OriginalTable'));

              LET vLoop = $(vDateEnd)-$(vDateStart)+1;

             

                  FinalTable:

                  LOAD

                  Peek('Employee',$(i)-1,'OriginalTable') AS Employee_final,

                  Date($(vDateStart) + Iterno() - 1) as Date_Final

                  AutoGenerate 1 WHILE Iterno() <= $(vLoop);

           

          NEXT

           

          Hope it helps for someone else!