Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tan_chungkam
New Contributor III

to show the very first (original) delivery week

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
1 Solution

Accepted Solutions
MVP
MVP

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

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;

7 Replies
Employee
Employee

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

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

MVP
MVP

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

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
Valued Contributor II

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

Please try below

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

tan_chungkam
New Contributor III

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

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.

MVP
MVP

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

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. CONCATENATE LOAD // Will force concatenate all the Sheet1 sheets from all xlsx files with the above table
  3.     * 
  4. FROM [lib://testing/*.xlsx] 
  5. (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
New Contributor III

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

Hi petter-s‌,

okk understand.

thanks for the help. Have a nice weekend.

MVP
MVP

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

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

Community Browser