Left Join load with where clause

[#1]

Hi,

I am working on a problem for some days now, but can’t find the solution.

The problem is as follows: ( I also attach the word doc with same description, but maybe more readable)

I have 2 tables A and B which I want to use in my QV script to make a new result
table C.

Tabel A: Table with saleslines per Dessin, having a Salesdate per line.

Dessin	Salesno	Salesdate
1020	1	01-06-2003
1021	2	01-06-2006
1022	3	26-06-2013
1021	4	01-07-2010
1023	5	01-06-2011

Tabel B:
Table with exclusivity periods per dessin. As you can see 1 dessin (1021) can
be exclusive in more then 1 period, but there is no overlap in different
periods in table B for the same Dessin.

Dessin	desStartdate	desEnddate
1020	01-03-2003	01-03-2004
1021	01-03-2006	01-03-2007
1021	01-03-2010	01-03-2011
1023	01-03-2004	01-03-2005
1023	01-03-2007	01-03-2008

Problem:
For each line from table A I want to aff a column ‘Exclusive’ with value ‘Y’ or
‘N’

If A.Salesdate >= B.desStartdate and A.salesdate <= B.desEnddate THEN ‘Y’
ELSE ‘N’

If I do a left join the result table C will look like This:

Dessin	Salesno	Salesdate	Exclusive
1020	1	01-06-2003	Y
1021	2	01-06-2006	Y
1021	2	01-06-2006	N
1022	3	26-06-2013	-
1021	4	01-07-2010	N
1021	4	01-07-2010	Y
1023	5	01-06-2011	N
1023	5	01-06-2011	N

But what I
want is:

Dessin	Salesno	Salesdate	Exclusive
1020	1	01-06-2003	Y
1021	2	01-06-2010	Y
1022	3	26-06-2013	N
1021	4	01-07-2010	Y
1023	5	01-06-2011	N

I tried a lot of things in the QV-script:

One of the things I tried was a left join with a where clause saying:
where Salesdate >= desStartdate And Salesdate <= ;

But when I reload the script gives an error that Salesdate field is not found.

Please can anyone tell me how to do this in my QV script.

[#2]

The left join works nicely.

Only those rows that can be joined will be taken into consideration.

So you could just as well have a Left Join like this:

Left Join(Table A)

LOAD *,

          'Y' as Exclusive

Resident Table B;

[#3]

I tried this, but the result is the same, i still have the '-' instead of then 'N' for column Exclusive, if not a match was found.