Show only 2 latest rows

[#1]

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

[swuehl]

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;

[jagan · Accepted solution]

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.

[#4]

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

Best regards, Renskje