Multiple Exclusions

[agsearle]

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?

[#2]

Hi

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

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

[agsearle]

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!

[#4]

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

[agsearle]

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

Tried to get it to work, but no success.

[#6]

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')

[agsearle]

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

[#8]

Maybe NOT must before numbers:

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

[agsearle]

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.

[#10]

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?