to show the very first (original) delivery week

[tan_chungkam]

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:

Month	Order No.	Del. week	Original Del. week
Jan	22222	201805	-
Feb	22222	201810	201805
Mar	22222	201812	201805

[Lisa_P]

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.];

[petter · Accepted solution]

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;

[sunilkumarqv]

Please try below

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

[tan_chungkam]

petter-s‌,

Thanks for the clear explanation and solution.

Actually the attached excel files are just a stimulation of my real files.

It does look a bit different from the real one.

The real one has more columns.

Just another question:

what if all the excel files have different column e.g. Files A has 10 columns and Files B has 20 columns.

Does the "load *.xlsx" still work with it?

Thanks a million.

[petter]

It can work with different columns too. You will have to use forced concatenation and a little "trick" to enable that by having an empty table that can be concatenated to before the load statement for the sheets.

1. OpenSalesOrder: LOAD * INLINE [Period]; // Creates an empty table with just one field
2. 
   
3. CONCATENATE LOAD // Will force concatenate all the Sheet1 sheets from all xlsx files with the above table
4.     * 
5. FROM [lib://testing/*.xlsx] 
6. (ooxml, embedded labels, table is Sheet1); 

A more serious challenge would be if not all the data is in a sheet named "Sheet1" ...

[tan_chungkam]

Hi petter-s‌,

okk understand.

thanks for the help. Have a nice weekend.

[petter]

You're welcome. Have a nice week-end too