Hi all,
I have a question about a join query. I have two tables, one has data of clients (with a start date en end date of the customer) and a table with months (or weeks, I will use the month table in this example).
Clients:
LOAD * INLINE [
Client, StartDate, EndDate
10, 1-1-2010, 25-2-2010
20, 2-1-2010, 13-3-2010
];
Months:
LOAD * INLINE [
Month, FistDay, LastDay
Jan, 1-1-2010, 31-1-2010
Febr, 1-2-2010, 28-2-2010
Mar, 1-3-2010, 31-3-2010
];
I would like to create a query like:
SELECT
c.Client
, c.StartDate
, c.EndDate
, m.Month
FROM Clients c
LEFT OUTER JOIN Months m ON
c.StartDate <= m.LastDay AND
c.EndDate >= m.FirstDay
The result should be like:
Client StartDate EndDate Month
10 1-1-2010 25-2-2010 Jan
10 1-1-2010 25-2-2010 Feb
20 2-1-2010 13-3-2010 Jan
20 2-1-2010 13-3-2010 Feb
20 2-1-2010 13-3-2010 Mar
The query works fine on a SQL server. Problem is that my tables are QlikView tables (via QVD or RESIDENT LOAD) and I do not have a connection to the SQL server.
What is the best way to do this in QlikView? The solution that I found was creating a key field in both tables with value 0. Then join both tables to a temptable. Then RESIDENT LOAD with a filter on the data (StartDate <= LastDay AND EndDate >= FirstDay). The problem with my solution is that it first creates a lot of records in the temptable.
Is there a better way to script that in QlikView?
Thanks in advance!
Cheers,
Werner
