topic Re: Show only 2 latest rows in QlikView

Show only 2 latest rows

Tue, 22 Jan 2013 13:35:39 GMT

Hi,

I'm not very experienced in QlikView and have a problem, I'd like to solve.

I have a table (in Access):

In my search, I only want to include the rows with black writing, disregarding the rows with red writing. This means that for each test subject, I only find the two rows with the most recent dates. I'm pretty sure it can be done, but how?

I appreciate your help.

Best regards, Renskje

Re: Show only 2 latest rows

swuehl — Tue, 22 Jan 2013 14:04:52 GMT

Maybe like this:

INPUT:

LOAD ID,

Brand,

[Test subject],

Ear,

Date,

VentType,

VentSize,

User

FROM

[Table HA.xlsx]

(ooxml, embedded labels, table is Sheet1);

MAX:

LOAD [Test subject]&'-'&timestamp(floor(max(Date))) as MaxSubjectDate,

[Test subject]

resident INPUT

where len(trim([Test subject])) group by [Test subject] ;

RESULT:

Noconcatenate LOAD *

Resident INPUT

where exists(MaxSubjectDate,[Test subject]&'-'&timestamp(floor(Date)));

drop tables INPUT, MAX;

Re: Show only 2 latest rows

jagan — Tue, 22 Jan 2013 14:09:54 GMT

Hi,

Try this script

OrderTemp:

LOAD ID,

Brand,

[Test subject],

Ear,

Date,

VentType,

VentSize,

User

FROM

[Table HA.xlsx]

(ooxml, embedded labels, table is Sheet1);

Order:

LOAD

WHERE RowNo < 3;

LOAD

If(Previous([Test subject]) <> [Test subject], 1, Peek(RowNo) + 1) AS RowNo,

Resident OrderTemp

Order by [Test subject], ID Desc;

DROP TABLE OrderTemp;

PFA file for solution.

Regards,

Jagan.

Re: Show only 2 latest rows

Tue, 22 Jan 2013 14:43:24 GMT

Great, that works! Thanks a lot. Very helpful with the attached file.

Best regards, Renskje