Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Left Join load with where clause

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.

Tags (1)
11 Replies
Or
Valued Contributor II

Re: Left Join load with where clause

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
Contributor III

Re: Left Join load with where clause

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

gandalfgray
Valued Contributor

Re: Left Join load with where clause

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;

Not applicable

Re: Left Join load with where clause

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.

Not applicable

Re: Left Join load with where clause

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
Honored Contributor II

Re: Left Join load with where clause

See the attached file

gandalfgray
Valued Contributor

Re: Re: Left Join load with where clause

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.

Not applicable

Re: Left Join load with where clause

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

Not applicable

Re: Left Join load with where clause

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.

Community Browser