Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Script handling fields

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

1 Solution

Accepted Solutions
amars
Specialist
Specialist

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

View solution in original post

6 Replies
amars
Specialist
Specialist

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

Not applicable
Author

Sorry, the file is empty

Not applicable
Author

Sorry I wrong 😞 The file is correct.

I try your solution

somenathroy
Creator III
Creator III

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;

Not applicable
Author

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
Creator III
Creator III

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

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