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 only the most recent

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.

TicketDate
101/01/2013
102/01/2013
103/01/2013
201/01/2013
205/01/2013
301/01/2013
407/01/2013
408/01/2013
401/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.

1 Solution

Accepted Solutions
its_anandrjs

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

View solution in original post

6 Replies
Anonymous
Not applicable
Author

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

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
MVP
MVP

another one

2.png

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;

Not applicable
Author

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
Specialist II
Specialist II

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
Specialist II
Specialist II

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;