2 Replies Latest reply: Mar 20, 2012 4:11 AM by V Butler RSS

    Excluding rows based on values in other rows

    V Butler

      Hi there,

       

      Below is an example of my QlikView query I need help with, followed by an example of the resultant dataset.

      Query:

      LOAD DISTINCT

           ISSUEID,

           CODE,

           DATE,

           TYPE,

           ACTION   

      FROM

                [D:\DATA.qvd] (qvd)

      WHERE (DATE > Date(AddYears(Today(),-2)));

       

      Dataset:

      dataset.jpg

      Now, I need to exclude not only the rows where the ACTION = 'Withdrawn', but I also need to exclude all rows that belong to the same ISSUEID and have the same TYPE (in this case, 'Short Term Rating') as the 'Withdrawn' row - so, in the above example, I need to retain the rows where the TYPE='Long Term Rating', but I want to get rid of the rows with a TYPE of 'Short Term Rating', because one of them has an ACTION of 'Withdrawn'.

       

      So I need to find a way to exclude rows which share the same values (ISSUEID, ACTION and TYPE) in another row - how can I do this in a LOAD statement?

       

      Thanks so much,

       

      VB

        • Excluding rows based on values in other rows
          Brandon Apperson

          I'm not quite sure if this will help but have you tried the Peek function?

           

          It allows you to see the previous rows data.

           

          For example:

           

          peek([Fieldname]) will give you the value of Fieldname from the previous record read.

           

          For more information, in Qlikview click on help->contents->index tab->type in "peek".

           

          Hope this helps!

            • Excluding rows based on values in other rows
              V Butler

              Thank you, but I don't think peek() will help, because the rows I need to exclude may not be immediately preceding each other - they may not be in consecutive rows within the dataset. What I'd like is the QlikView equivalent of this PL/SQL:

              SELECT * from data WHERE issueid IN (SELECT issueid FROM data WHERE action = 'Withdrawn' AND type = 'Long Term Rating');

               

              Thanks anyway.