topic Re: help using a 'BETWEEN' join on tables from different datasources in QlikView

help using a 'BETWEEN' join on tables from different datasources

Tue, 14 Sep 2010 12:11:00 GMT

Hi all,

I have a query on my PC that uses the BETWEEN command in ORACLE

e.g.

transdate between period.start_date and period.end_date

I need to take the period table in our Oracle database, and join it in the same way with a table in SQL Server

so that I get the period name that each financial transaction belongs to.

Is there a way of doing this in QlikView script?

thanks in advance,

Matt

help using a 'BETWEEN' join on tables from different datasources

Tue, 14 Sep 2010 12:44:19 GMT

maybe, you can try with "link server" oracle to sql serve

help using a 'BETWEEN' join on tables from different datasources

Tue, 14 Sep 2010 12:47:19 GMT

Either you keep using the Between function in the SQL statement in your script.

Or you can have a look at interval function in the help file

Rgds,

Sébastien

Re: help using a 'BETWEEN' join on tables from different datasources

Miguel_Angel_Baeyens — Tue, 14 Sep 2010 13:05:09 GMT

Hello Matt,

If I understood your question right, this example may help

DatesTable:
LOAD * INLINE [
Start, End, Name
01/01/2010, 31/01/2010, First
01/02/2010, 28/02/2010, Second
]; 
FactsTable:
LOAD * INLINE [
ID, Date, Amount
A, 03/02/2010, 100
B, 16/01/2010, 200
]; 
FactsDates:
INTERVALMATCH (Date) LEFT JOIN LOAD Start, 
     End
RESIDENT DatesTable;
LEFT JOIN (FactsTable) LOAD * 
RESIDENT DatesTable; 
DROP TABLE DatesTable;

Is that correct?

help using a 'BETWEEN' join on tables from different datasources

Tue, 14 Sep 2010 14:51:53 GMT

that is absolutely correct!

thank you very much.

Strangely enough I have not used the interval match function before..

by the look of it, its something that I may want to use a lot going forward

Matt

Re: help using a 'BETWEEN' join on tables from different datasources

Anonymous — Mon, 25 Aug 2014 04:12:02 GMT

Hi Miguel,

Thanks for you nice explanation...

Re: help using a 'BETWEEN' join on tables from different datasources

Anonymous — Fri, 19 Dec 2014 16:57:42 GMT

This was a very simple and clear example on Interval Match. Just awesome. Thanks Miguel. I still have doubts on the resident loads and how it solved this particular problem using Interval match. I was able to use it to get the right answer as in the image above but have some questions. When you drop the dates table, I am still able to use the field "name" because I brought that field from that table to facts table using a left join, correct..?

INTERVALMATCH (Date) LEFT JOIN LOAD Start,
End
RESIDENT DatesTable;

Here, i am doing a left join on which table?

LEFT JOIN (FactsTable) LOAD *
RESIDENT DatesTable;

Here, I am bringing fields from dates table to Factstable, correct?

3) and Finally why did I use Resident Loads twice..?

I am finding such meaningful answers in this community. Thanks to everyone involved.

Happy Friday :

Re: help using a 'BETWEEN' join on tables from different datasources

Anonymous — Fri, 19 Dec 2014 20:33:20 GMT

No Reply??????