topic LEFT JOIN using WHERE in QlikView

LEFT JOIN using WHERE

wernerprovelu — Tue, 07 Dec 2010 14:21:30 GMT

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

LEFT JOIN using WHERE

Tue, 07 Dec 2010 14:39:21 GMT

Hi,

If i am able to understand this will help you to solve your problem.

Clients:

LOAD * INLINE [

Client, StartDate, EndDate

10, 1-1-2010, 25-2-2010

20, 2-1-2010, 13-3-2010

];

left join

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

];

Test:
Load *
Resident Clients where StartDate <=LastDay and EndDate>=FistDay;

Regards,

Ravi

LEFT JOIN using WHERE

wernerprovelu — Tue, 07 Dec 2010 14:58:13 GMT

Hi Ravi,

Thanx for your answer.

After the left join (before Test:), there could be a lot of records (not in this example, but with 50.000 clients and 4 years (208 weeks) there are more then 10M records). That could give some memory issues.

Werner