topic Multiple Exclusions in QlikView

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 17:10:38 GMT

I have two sets of data, a series of numbers, and a series of dates, in two separate tables. The numbers are transaction numbers and the dates are the dates they were applied.

Numbers Example:
6000-0010
6000-0030
6200-0030
6200-0060

I need to exclude, in the script preferably, a selection of the numbers, eg 6000-0010 and 6200-0030, but only if their corresponding date is greater than 30/09/2008.

I've been trying for ages to get this done, but have failed miserably, can anyone help?

Multiple Exclusions

Wed, 04 Nov 2009 17:52:49 GMT

What is the source for your data (SQL, Excel, CVS)?

How do the two tables join (what is the common key between them)?

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 18:01:25 GMT

The source of the data is from a QVD previously loaded from SAP.

Also my mistake there is no key, as the two fields are in the same table, sorry!

Multiple Exclusions

Wed, 04 Nov 2009 18:19:05 GMT

Take a look at the MATCH() function, I'm sure you could achieve this with two MATCH statements.

See the entry here, with more details at the bottom:

http://community.qlik.com/blogs/qlikviewblogs/archive/2008/10/25/the-match-function.aspx

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 19:09:06 GMT

Not that familiar with MATCH. Also I want to exclude from selection, not match?

Tried to get it to work, but no success.

Multiple Exclusions

Wed, 04 Nov 2009 19:27:37 GMT

HI maybe something like :


SELECT *
FROM table
WHERE (numbers NOT '6000-0010' AND DATE > '30/09/2008')
OR (numbers NOT '6000-0030' AND DATE > '30/09/2008')
OR (numbers NOT '6200-0030' AND DATE > '30/09/2008')

OR (numbers NOT '6200-0060' AND DATE > '30/09/2008')

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 21:39:32 GMT

Sorry didn't like that, error message says it's expecting a '(' ?

Multiple Exclusions

Wed, 04 Nov 2009 21:48:16 GMT

Maybe NOT must before numbers:

AND NOT (numbers = '1234' and date > 'dateyouneed')

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 22:02:58 GMT

The script didn't fail this time, but the result isn't right. This is what I have at the moment:


DFKKOPBW_TEMP_12:
LOAD Due_Date as Due_Date_12,
 Main_Transaction&'-'&Subtransaction_For_Document_Item as Main_Sub_Trans_12,
 CA as CA_12
FROM  (qvd)
WHERE EXISTS (CA_12,CA);
DFKKOPBW_12:
LOAD Due_Date_12,
 Main_Sub_Trans_12,
 CA_12
RESIDENT DFKKOPBW_TEMP_12
WHERE NOT (Main_Sub_Trans_12 = '6000-0010' AND Due_Date_12 > '30/09/2008');

Essentially what I need is to exclude '6000-0010' from the result, but only if the Due Date is greater than '30/09/2008', as there are still some '6000-0010' entries where the Due Date is less than 30/09/2008 that I want to keep.

Multiple Exclusions

Wed, 04 Nov 2009 22:22:00 GMT

Is the date format you enter correct?

how is it stored in the QVD?

What result do you get? Nothing from 6000-0010 or all or something in between?

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 22:38:16 GMT

The date format is stored as 2008-09-30, but I've tried both formats, usually the 30/09/2008 format works fine.

In the result I get all the 6000-0010 entries, regardless of the date.

Multiple Exclusions

Wed, 04 Nov 2009 22:43:07 GMT

Thats odd,

it should work if you put WHERE NOT (Main_Sub_Trans_12 = '6000-0010' AND Due_Date_12 > '2008-09-30');

Maybe your dateformat is SET DateFormat='DD/MM/YYYY';

Try to load only the

WHERE NOT Due_Date_12 > '2008-09-30');

See if that works. Then youll know its not your dateformat.

rgds

Rey-man

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 23:15:03 GMT

It didn't work with any date format, but I think I know where the problem might be.

As I wanted to join two fields together, I load them as a temporary table, then using RESIDENT reload the table trying to exclude as above. But I think it's still linking with the data in the temporary table, and hence all these solutions aren't working?

Multiple Exclusions

agsearle — Wed, 04 Nov 2009 23:32:09 GMT

Sorted!!!!!

Your suggestion works fine if I use it before the RESIDENT table, so I've removed this part and it works fine. Script now looks like:


DFKKOPBW_TEMP_12:
LOAD Due_Date as Due_Date_12,
 Main_Transaction&'-'&Subtransaction_For_Document_Item as Main_Sub_Trans_12,
 CA as CA_12
FROM  (qvd)
WHERE EXISTS (CA_12,CA)
AND NOT (Main_Transaction = '6000' AND Subtransaction_For_Document_Item = '0030' AND Due_Date > '30/09/2008');

Thanks for all your help, couldn't have done it without you.

Regards