Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
maybe, you can try with "link server" oracle to sql serve
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
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?
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
Hi Miguel,
Thanks for you nice explanation...
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..?
1)
Here, i am doing a left join on which table?
2)
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 :
No Reply??????