Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (3)
1 Solution

Accepted Solutions

Re: load only the most recent

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

6 Replies
mov
Esteemed Contributor III

Re: load only the most recent

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

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

MVP
MVP

Re: load only the most recent

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

Re: load only the most recent

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
Valued Contributor II

Re: load only the most recent

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
Valued Contributor II

Re: load only the most recent

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;

Community Browser