load only the most recent

[#1]

Hi,

I've a problem, I try load from excel file only most recent row. one ticket have many dates, and I need take the most recent.

Ticket	Date
1	01/01/2013
1	02/01/2013
1	03/01/2013
2	01/01/2013
2	05/01/2013
3	01/01/2013
4	07/01/2013
4	08/01/2013
4	01/01/2013

It's easy to my in sql, but i can't do it with Qlikview table. Can someone help me??

P.D.: Sorry for my English.

[Anonymous]

Jose,

This should work:

data:
LOAD
Ticket,
Date
FROM <excel file>;

INNER JOIN (data) LOAD
Ticket,
date(max(Date)) as Date
RESIDENT data
GROUP BY Ticket;

Regards,
Michael

[its_anandrjs · Accepted solution]

Load like this

A:

LOAD * Inline

[

Ticket, Date

1, 01/01/2013

1, 02/01/2013

1, 03/01/2013

2, 01/01/2013

2, 05/01/2013

3, 01/01/2013

4, 07/01/2013

4,  08/01/2013

4, 01/01/2013

];

Right Join

LOAD

Ticket,

MaxString(Date) as MaxDate

Resident A

Group By Ticket;

Hope this helps

Thanks & Regards

[maxgro]

another one

Directory;

S:

LOAD Ticket,

     Date

FROM

ticket.xlsx

(ooxml, embedded labels, table is Sheet1);

L:

NoConcatenate

load *

Resident S

where Peek(Ticket)<> Ticket

order by Ticket, Date desc;

DROP Table S;

[#5]

Thanks to all.

finally, I use this:

Temporal:

LOAD

Ticket,

MaxString(Date) as MaxDate,

FirstSortedValue(Status,-date]) as Max_status

Resident Historico

Group By Ticket;

[nizamsha]

A:

LOAD * Inline

[

Ticket, Date

1, 01/01/2013

1, 02/01/2013

1, 03/01/2013

2, 01/01/2013

2, 05/01/2013

3, 01/01/2013

4, 07/01/2013

4,  08/01/2013

4, 01/01/2013

];

LOAD

Ticket AS  Ticket1 ,

MaxString(Date) as Date1

Resident A

Group By Ticket;

DROP Field Date;

[nizamsha]

A:

LOAD * Inline

[

Ticket, Date

1, 01/01/2013

1, 02/01/2013

1, 03/01/2013

2, 01/01/2013

2, 05/01/2013

3, 01/01/2013

4, 07/01/2013

4,  08/01/2013

4, 01/01/2013

];

LOAD

Ticket AS  Ticket1 ,

max(Date( Date#( Date,'MM/DD/YYYY'))) as Date1

Resident A

Group By Ticket;

DROP Field Date;