topic Excluding rows based on values in other rows in QlikView

Excluding rows based on values in other rows

Anonymous — Mon, 19 Mar 2012 14:20:04 GMT

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:

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,

Excluding rows based on values in other rows

Mon, 19 Mar 2012 17:57:17 GMT

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

Anonymous — Tue, 20 Mar 2012 08:11:52 GMT

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.