topic Muliple Tables (Join Where) in QlikView

Muliple Tables (Join Where)

Fri, 19 Mar 2010 20:32:53 GMT

Here is how the data is setup:

Table 1 (OrderDate, Territory)
Table 2 (Territory, Staff, StartDate, EndDate)

I'm trying to pull in the staff into table one where the orderdate is within the startdate and enddate.

Any ideas on how to accomplish that?

Muliple Tables (Join Where)

Fri, 19 Mar 2010 21:09:55 GMT

Oy - beyond hokey, but this could work:

TEMPTable:

load
OrderDate,
Territory
from table1

LEFT JOIN LOAD
Territory,
Staff,
StartDate,
EndDate
from table2;

TEMPTable2:
LOAD
Territory,
Staff,
IF(OrderDate>=StartDate and OrderDate<=EndDate, OrderDate) as OrderDate
RESIDENT TEMPTable;

FinalTable:
LOAD *
RESIDENT TEMPTAble2
WHERE NOT ISNULL(OrderDate);

DROP TABLE TEMPTable;
DROP TABLE TEMPTable2;

There must be a more elegant way to do it. I hate dealing with that many temp tables.

Muliple Tables (Join Where)

Fri, 26 Mar 2010 15:10:12 GMT

Thanks Sally. That worked!

Muliple Tables (Join Where)

johnw — Fri, 26 Mar 2010 21:24:49 GMT

I think this (untested):

LEFT JOIN ([Table 1])
INTERVALMATCH (OrderDate,Territory)
LOAD
StartDate
,EndDate
,Territory
RESIDENT [Table 2]
;
LEFT JOIN ([Table 1])
LOAD *
RESIDENT [Table 2]
;
DROP TABLE
[Table 2]
;
DROP FIELDS
StartDate
,EndDate
;

Even if that's not exactly it, I think that's the basic idea. You use intervalmatch to match up the order date and territory to the start and end dates by territory. The first left join should, I think, put the start and end date on Table 1. At that point you have territory, start date and end date on table 1, so one more left join gets you the staff. After that, I assume you no longer need table 2 or the start and end dates.

Muliple Tables (Join Where)

Fri, 26 Mar 2010 22:43:16 GMT

I agree with John's method.

But perhaps you should add DISTINCT in this place:


LEFT JOIN ([Table 1])
INTERVALMATCH (OrderDate,Territory)
LOAD DISTINCT
 StartDate
,EndDate
,Territory
RESIDENT [Table 2]
;

That way a each combination of Territory, StartDate and EndDate is unique. If each value in field Staff can only have one combination of those three fields, you don't need the DISTINCT of course.

Muliple Tables (Join Where)

Mon, 29 Mar 2010 14:04:36 GMT

Aha. I knew there had to be a more elegant way of doing it.

Muliple Tables (Join Where)

Mon, 29 Mar 2010 14:41:45 GMT

Thanks guys. I'll give this method a try.