topic Re: WHERE in JOIN when loading in QlikView

WHERE in JOIN when loading

hschultz — Mon, 19 Nov 2012 09:18:47 GMT

Hi there

I would like to know if it is possible to put a condition on the a join when adding 1 table to another.

What needs to happen is that I have 1 table with start & end dates with a speciffic price.

So with my join, when the date is in a speciffic range it needs to join the price from that range.

The way i understand QV does an automattic join if the colum names are the same. So just joining on product doesn't give the correct results.

And when i try to join where date > start and < end, then I get an error that Table1.date doesn't exist in Table2.

Here is a simple example:

Sales table:

Product	Sell Date
Coke	02/02/2012
Coke	10/10/2012

Pricing table:

Product	Start Date	End Date	Price
Coke	01/01/2012	30/06/2012	10.00
Coke	01/07/2012	31/12/2012	25.00

So after the JOIN, i need the table to look like this:

Combined:

Product	Sell Date	Sell Price
Coke	02/02/2012	10.00
Coke	10/10/2012	25.00

Thank you in advance

Re: WHERE in JOIN when loading

Anonymous — Mon, 19 Nov 2012 09:21:57 GMT

Hi,

For this requirement you should look at using the Intervalmatch() function in QlikView. It does exactly what you need, i.e. match a value within an interval.

Re: WHERE in JOIN when loading

Gysbert_Wassenaar — Mon, 19 Nov 2012 09:29:34 GMT

See attached example.

Re: WHERE in JOIN when loading

hschultz — Mon, 19 Nov 2012 13:29:45 GMT

Thanx. This does seem like the function i require.

However, what happens is there for instance isn't an end date?

Like for instance the price says like that forever?

Re: WHERE in JOIN when loading

jfkinspari — Mon, 19 Nov 2012 13:36:12 GMT

My guess is that the interval will be ignored.

In such cases you should put in a fictional max date like 31-12-2099 - that way making sure the interval is always closed

Re: WHERE in JOIN when loading

hschultz — Wed, 21 Nov 2012 11:33:23 GMT

Ok i have tested this solution, but it doesn't seem to be working.

First i tried to adapt the same code to my enviroment, but i get NO results in the tables actually linking

-------------------------------------------

Left join (Timesheet1)

IntervalMatch(TS_NewDate2)

load Distinct [SLA_Start1],[SLA_End1]

Resident Projects;

Left join load * Resident Projects;

Drop Fields [SLA_Start1],[SLA_End1];

Drop table Projects;

------------------------------------------

Changing the code to add the date() function on line 3 like this -

load Distinct date([SLA_Start1]),date([SLA_End1])

Gives me results, but then against all lines, and not only where it is supposed to be applicable