topic Load Script handling fields in QlikView

Load Script handling fields

Thu, 25 Oct 2012 09:36:45 GMT

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

Re: Load Script handling fields

amars — Thu, 25 Oct 2012 09:46:51 GMT

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

Re: Load Script handling fields

Thu, 25 Oct 2012 09:50:03 GMT

Sorry, the file is empty

Re: Load Script handling fields

Thu, 25 Oct 2012 09:52:46 GMT

Sorry I wrong 😞 The file is correct.

I try your solution

Re: Load Script handling fields

somenathroy — Thu, 25 Oct 2012 09:57:22 GMT

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

900, 1, 01/01/2012

900, 1, 05/05/2012

900, 1, 09/08/2012];

//Solution 1:

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;

Re: Load Script handling fields

Thu, 25 Oct 2012 10:18:13 GMT

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

Re: Load Script handling fields

somenathroy — Thu, 25 Oct 2012 10:39:45 GMT

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

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