7 Replies Latest reply: Mar 7, 2014 10:09 AM by Robbie Evason RSS

    Create a table between two dates

    Robbie Evason

      Hello

      I have a table that contains two dates (Fig1) and i would like to calculate the days inbetween and display them like Fig2.

       

      What is the best way to create this table? I have created a 'For Next' to loop through the records in the script, but for thousands of orders this is very slow. Can i just generate the table without creating it in load script?

       

      Any help would be greatly received.

      Thankyou

       

      Fig1

      OrderNo.OrderDateDeliveryDate
      111101/04/201304/04/2013
      222203/05/201305/05/2013
      333304/05/201328/02/2014
      444412/12/201304/03/2014

       

       

      Fig2

      OderNoDateOutstandingDelivered
      111101/04/20131
      111102/04/20131
      111103/04/20131
      111104/04/20131
      222203/05/20131
      222204/05/20131
      222205/05/20131
        • Re: Create a table between two dates
          Vinay Naran

          why do you want to change? what is your objective?

           

          Do you want to count how many days between two dates?

           

          Vinay

           

           

           


          • Re: Create a table between two dates
            Byron Van Wyk

            Load this into your script

             

            Data:

            Load * inline [

            OrderNo, OrderDate, DeliveryDate

            1111, 01/04/2013, 04/04/2013

            2222, 03/05/2013, 05/05/2013

            3333, 04/05/2013, 28/02/2014

            4444, 12/12/2013, 04/03/2014];

             

            DataFinal:

            Load

              OrderNo,

              Date,

              if(Date<DeliveryDate,1,null()) as Outstanding,

              if(Date=DeliveryDate,1,null()) as Delivered;

            Load

              OrderNo,

              OrderDate,

              DeliveryDate,

              Date(OrderDate+IterNo()-1) as Date

            Resident Data

            While OrderDate+IterNo()-1 <= DeliveryDate;

             

            Drop Table Data;

              • Re: Create a table between two dates
                Robbie Evason

                Hi Byron,

                 

                Wow that seems to work great, but i am struggling to understand how it works...where did the 'Date' field come from??

                 

                Many thanks

                R

                  • Re: Create a table between two dates
                    Byron Van Wyk

                    Glad it did mate!

                     

                    The date field i created in the first load

                     

                    Load

                      OrderNo,

                      OrderDate,

                      DeliveryDate,

                      Date(OrderDate+IterNo()-1) as Date

                    Resident Data

                    While OrderDate+IterNo()-1 <= DeliveryDate;

                     

                    it creates a date for each date that appears between the conditional where clause. The -1 after iterno() is because iterno() starts with the value 1, and we want the first date not the next

                     

                    While OrderDate+IterNo()-1 <= DeliveryDate;


                    This is where it does a loop like check and creates our new table


                    DataFinal:

                    Load

                      OrderNo,

                      Date,

                      if(Date<DeliveryDate,1,null()) as Outstanding,

                      if(Date=DeliveryDate,1,null()) as Delivered;

                    Load

                      OrderNo,

                      OrderDate,

                      DeliveryDate,

                      Date(OrderDate+IterNo()-1) as Date

                    Resident Data

                    While OrderDate+IterNo()-1 <= DeliveryDate;

                     

                    The preceding load is where we get the final result you want. Hope that clears it up for you. Let me know

                     

                     

                    Cheers,

                    Byron



                • Re: Create a table between two dates
                  Manish Kachhia

                  Load

                    OrderNo,

                    OrderDate,

                    DeliveryDate,

                    Date(OrderDate+IterNo()-1) as Date

                  Resident Data

                  While OrderDate+IterNo()-1 <= DeliveryDate;

                   

                  Here the table is giving you dates for OrderDate upto DeliveryDate by iteration.

                   

                  Now this Date is being used in Preceding Load to find out Outstanding and Delivered.

                  In other ways the above script can be used as below, but Preceding Load is much faster ...

                   

                  Data:

                  Load * inline [

                  OrderNo, OrderDate, DeliveryDate

                  1111, 01/04/2013, 04/04/2013

                  2222, 03/05/2013, 05/05/2013

                  3333, 04/05/2013, 28/02/2014

                  4444, 12/12/2013, 04/03/2014];

                   

                  DataFinal:

                  Load

                    OrderNo,

                    OrderDate,

                    DeliveryDate,

                    Date(OrderDate+IterNo()-1) as Date

                  Resident Data

                  While OrderDate+IterNo()-1 <= DeliveryDate;

                   

                   

                  FINAL:

                  Load

                    OrderNo,

                    Date,

                    if(Date<DeliveryDate,1,null()) as Outstanding,

                    if(Date=DeliveryDate,1,null()) as Delivered

                  Resident DataFinal;

                   

                   

                  Drop Table Data, DataFinal;