Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vb_qv_11
New Contributor II

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

Excluding rows based on values in other rows

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
New Contributor II

Excluding rows based on values in other rows

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.

Community Browser