3 Replies Latest reply: Jan 22, 2013 9:43 AM by Renskje Hietkamp RSS

    Show only 2 latest rows

    Renskje Hietkamp

      Hi,

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

      I have a table (in Access):

      Table HA.png

      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
          Stefan Wühl

          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 mohan rao appala

            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.