Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
vb_qv_11
Contributor III
Contributor III

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

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!

vb_qv_11
Contributor III
Contributor III

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.