Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
hschultz
Partner - Creator
Partner - Creator

WHERE in JOIN when loading

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:

ProductSell Date
Coke02/02/2012
Coke10/10/2012

Pricing table:

ProductStart DateEnd DatePrice
Coke01/01/201230/06/201210.00
Coke01/07/201231/12/201225.00

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

Combined:

ProductSell DateSell Price
Coke02/02/201210.00
Coke10/10/201225.00

Thank you in advance

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Anonymous
Not applicable

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.

Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand
hschultz
Partner - Creator
Partner - Creator
Author

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?

jfkinspari
Partner - Specialist
Partner - Specialist

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

hschultz
Partner - Creator
Partner - Creator
Author

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