7 Replies Latest reply: Jul 27, 2018 5:46 AM by Petter Skjolden RSS

    to show the very first (original) delivery week

    Chung Kam Tan

      Hi lads,

       

      I have 5 months (Jan - May) sales order excel file.

       

      I would like to create a column to show the order's very first delivery week.

       

      I had used Previous function but it show the last month delivery week instead of the very first delivery week.

       

      I would like to do the scripting for original del. week column.

       

      Is there any scripting to do it?

       

      Please find attached all my excel files and scripting. Thanks for the help.

       

      My expected result would be:

      MonthOrder No.Del. weekOriginal Del. week
      Jan22222201805-
      Feb22222201810201805
      Mar22222201812201805
        • Re: to show the very first (original) delivery week
          Lisa Phillips

          Hi Chung,

           

          Rather that the final table, try just doing an aggregation table to find the Min("Delivery Week") by Order No.

           

          Left Join

          Load "Order No.",

          Min("Delivery Week") as [Original Del. Week]

              Resident OpenSalesOrder

              Group by [Order No.];

          • Re: to show the very first (original) delivery week
            Petter Skjolden

            Hi Chung,

             

            You simply have to change one line of your script to use Peek() instead of Previous() (since Previous does not know about newly created fields in your load statement like "Original Del. Week") and use the original field when it is a new order like this:

             

            //    If(Previous("Order No.")="Order No.", Previous("Delivery Week")) as "Original Del. Week"

                If(Previous("Order No.")="Order No.", Peek("Original Del. Week"),"Delivery Week") as "Original Del. Week"

             

             

            HOWEVER:

             

            I would also advice you to include Period in your Order By clause to make sure that the months follow in the right order.

             

            You can also simplify the loading of all period sheets by load *.xlsx in your load statement like this as long as you only have period sheets in the folder:

             

            LOAD
                "Period",
                Dual("Month",Period) AS Month,
                "Order Date",
                "Order No.",
                "Delivery Week"
            FROM [lib://testing/*.xlsx]
            (ooxml, embedded labels, table is Sheet1);
            
            
            
            

             

            Finally do remove the Right Join (OpenSalesOrder) since this is a superfluous operation and you don't need the original OpenSalesOrder table after the resident load you can just drop it and save processing time.

             

            So your final script would, after my suggestions, look like this:

             

            OpenSalesOrder:
            LOAD
                "Period",
                Dual("Month",Period) AS Month,
                "Order Date",
                "Order No.",
                "Delivery Week"
            FROM [lib://testing/*.xlsx]
            (ooxml, embedded labels, table is Sheet1);
            
            
            FinalTable:
            LOAD
             "Period",
                Month,
                "Order Date",
                "Order No.",
                "Delivery Week",
                If(Peek("Order No.")="Order No.", 'Old', 'New') as "OrderType",
                If(Peek("Order No.")="Order No.", Peek("Original Del. Week") , "Delivery Week") as "Original Del. Week"
            Resident OpenSalesOrder
            Order by "Order No.","Period";
            
            
            DROP TABLE OpenSalesOrder;
            
            
            
            • Re: to show the very first (original) delivery week
              sunil kumar

              Please try below

              =if ( [Order No.]= previous ( [Order No.] ,  peek([Original Del.  week]), [Del.  week])    as  [Original Del.  week]