Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Excluding rows based on values in other rows

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

           (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

2 Replies
Not applicable
Author

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!

Anonymous
Not applicable
Author

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.