topic Re: load only the most recent in QlikView

load only the most recent

Wed, 26 Mar 2014 12:30:28 GMT

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.

Re: load only the most recent

Anonymous — Wed, 26 Mar 2014 12:36:47 GMT

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

Re: load only the most recent

its_anandrjs — Wed, 26 Mar 2014 12:43:13 GMT

Load like this

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

Re: load only the most recent

maxgro — Wed, 26 Mar 2014 12:46:29 GMT

another one

Directory;

LOAD Ticket,

Date

FROM

ticket.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

load *

Resident S

where Peek(Ticket)<> Ticket

order by Ticket, Date desc;

DROP Table S;

Re: load only the most recent

Thu, 27 Mar 2014 13:07:07 GMT

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;

Re: load only the most recent

nizamsha — Thu, 27 Mar 2014 13:46:45 GMT

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;

Re: load only the most recent

nizamsha — Thu, 27 Mar 2014 13:50:05 GMT

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;