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.
