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.

[Or]

If I am not mistaken, this should be achievable using IntervalMatch() extended syntax. If you can't get it done using the example in the QlikView documentation, post again and I'll try and whip up the syntax - it should look something like:

A:

Load * from A;

B:

Load * from B;

Left join A

IntervalMatch(Salesdate,Dessin) Load desStartdate, desEnddate, Dessin resident B;

At which point you have a table with the matched intervals, and just need to add the "Exclusive" field.

[michael_maeuser]

orsh_ is right. interval match should help. heres a sample qvw

[gandalfgray]

And I also added the right join below to get the "Exclusive" field.

Left Join (A)

IntervalMatch (Salesdate, Dessin)

LOAD desStartdate, desEnddate,

    Dessin

Resident B;

Right Join(A)

LOAD *,

    if(IsNull(desStartdate),'N','Y') As Exclusive

Resident A;

[#5]

I forgot to mention that i am not an axperienced QV developer
I will try to get my script working using you're help and will let you know the outcome.

Thanks in advance.

[#6]

Hi,

i run some tests and it looks like it is working fine.

I still have 1 question:
I loooked at the syntax which looks like:
intervalmatch matchfield, keyfield1

LOAD lower limt, upper limit, keyfield1

Suppose i want to load other fields from table B such as color. How an i load this field then, because it looks like only keyfields can be loaded.

Is this the solution:

intervalmatch matchfield, keyfield1

LOAD lower limt, upper limit, keyfield1, color

Or this:
intervalmatch matchfield, keyfield1, color

LOAD lower limt, upper limit, keyfield1, color

Or is it not possible to load additional non key fields?

Great thanks for your help soo far.

[er_mohit]

See the attached file

[gandalfgray]

Yes that is the way to do it.

You don't need the

tmpBridgeTable: -line though.

Just

Left Join(Table1)

intervalmatch (Salesdate,Dessin) load

    desStartdate,

    desEnddate,

    Dessin

resident Table2;

Left Join(Table1)

LOAD *

Resident Table2;

DROP Table Table2;

will do.

[#9]

Maybe i was not clear, or maybe i don't understand the solution.

It is great that you posted a real working qvw file, but i still don't know how to add more fields then just the key fields.

I changed my document using the extra column 'color' to show what i mean.
Maybe you can tell me how to do this? (And add more fields from table B as length and weight.

Sorry if you already did, and i did not get the message

[#10]

Hi,

I'm almost finished, and i have teh result i wanted except for 1 small issue:
I would like to have the field Exclusive as a 'N' instead of a '-'.

I used the solution with :

if(IsNull(desStartdate),'N','Y') As Exclusive

But for some reason it does not work.

Find attached my qvw file and solution.

Many thanks to all who helped me out.