topic Re: Left Join load with where clause in QlikView

Left Join load with where clause

Tue, 10 Sep 2013 13:01:43 GMT

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.

Re: Left Join load with where clause

Or — Tue, 10 Sep 2013 13:33:57 GMT

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:

Load * from A;

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.

Re: Left Join load with where clause

michael_maeuser — Tue, 10 Sep 2013 13:45:52 GMT

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

Re: Left Join load with where clause

gandalfgray — Tue, 10 Sep 2013 13:49:53 GMT

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;

Re: Left Join load with where clause

Tue, 10 Sep 2013 13:53:51 GMT

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.

Re: Left Join load with where clause

Wed, 11 Sep 2013 09:48:07 GMT

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.

Re: Left Join load with where clause

er_mohit — Wed, 11 Sep 2013 10:48:17 GMT

See the attached file

Re: Re: Left Join load with where clause

gandalfgray — Wed, 11 Sep 2013 13:50:48 GMT

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.

Re: Left Join load with where clause

Wed, 11 Sep 2013 14:31:21 GMT

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

Re: Left Join load with where clause

Wed, 11 Sep 2013 15:16:34 GMT

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.

Re: Left Join load with where clause

Wed, 11 Sep 2013 15:58:32 GMT

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;

Re: Left Join load with where clause

Thu, 12 Sep 2013 05:59:19 GMT

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.