Discussion Board for collaboration on QlikView Scripting.
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
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
Go to Solution.
See attached Example
You can drop the Test table once you have got the desired output.
I just kept it for your reference.
Sorry, the file is empty
Sorry I wrong :-( The file is correct.
I try your solution
A:LOAD * Inline[Order, Position, Delivery Date
900, 1, 01/01/2012
900, 1, 05/05/2012
900, 1, 09/08/2012];
LOAD Order, Position, Max(Date(Date#([Delivery Date],'DD/MM/YYYY'),'DD/MM/YYYY')) as [Calculated Last Delivery] Resident A Group By Order, Position;
NoConcatenateLOAD * Resident A;Left JoinLOAD 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;
I have a little issue more.
I have two table created by qlikview, because i have to convert the date
I import Table1
900 1 20120101
900 1 20120505
900 1 20120809
so i change the format
SQL SELECT Order,
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
I should use the DeliveryDate1 but qlikview doesn't recognize it inside the table Table1
If extracted filed, Delivery Date is in format 'YYYYMMDD', in that case modified function is :