Load Script handling fields

[#1]

Hi,

I have a table with Order, Position and Delivery Date.

A single Order, Position can has several records because we have several delivery date. For Example

Order     Position     Delivery Date

900          1               01/01/2012

900          1               05/05/2012

900          1               09/08/2012

I should have only one record with the last delivery Date - Solution 1

Order     Position     Delivery Date

900          1              09/08/2012

or. another solution...( Solution 2 )

Order     Position     Delivery Date     Calculated Last Delivery

900          1               01/01/2012     09/08/2012

900          1               05/05/2012     09/08/2012

900          1               09/08/2012     09/08/2012

How can I do it in Load Script ?

Thanks a lot

[amars · Accepted solution]

Hi,

See attached Example

You can drop the Test table once you have got the desired output.

I just kept it for your reference.

Thanks.

Amar

[#3]

Sorry, the file is empty

[#4]

Sorry I wrong 😞 The file is correct.

I try your solution

[somenathroy]

A:
LOAD * Inline
[Order,     Position,     Delivery Date

900,          1,               01/01/2012

900,          1,               05/05/2012

900,          1,               09/08/2012];

//Solution 1:

B:

LOAD Order, Position, Max(Date(Date#([Delivery Date],'DD/MM/YYYY'),'DD/MM/YYYY')) as [Calculated Last Delivery] Resident A Group By Order, Position;

//Solution 2:
B:

NoConcatenate
LOAD * Resident A;
Left Join
LOAD Order, Position, Max(Date(Date#([Delivery Date],'DD/MM/YYYY'),'DD/MM/YYYY')) as [Calculated Last Delivery] Resident A Group By Order, Position;

DROP Table A;

[#6]

I have a little issue more.

I have two table created by qlikview, because i have to convert the date

I import Table1

Order     Position     Delivery Date

900          1               20120101

900          1               20120505

900          1               20120809

so i change the format

Table1:

SQL SELECT Order,

Position,

DeliveryDate

FROM Table_Orders;

Load Order

Position,

date(timestamp(DeliveryDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as DeliveryDate1 resident Table_Orders;

So in this case I have two tables, Table1 and Table1-1 generated by QlikView.

Table1-1 generated from QlikView

Order     Position     Delivery Date1

900          1               01/01/2012

900          1               05/05/2012

900          1               09/08/2012

I should use the DeliveryDate1 but qlikview doesn't recognize it inside the table Table1

[somenathroy]

If extracted filed, Delivery Date is in format 'YYYYMMDD', in that case modified function is :

Max(Date(Date#([Delivery Date],'YYYYMMDD'),'DD/MM/YYYY')).