Join between DIMENSION & FACT in QLIKVIEW

[hkg_qlik]

I have REVENUE FACT TABLE with 1000 records and I have a TIME DIMENSION with 16000 records.

Now I am using DATE WID to join two table by using 'AS' in the FACT Table.

I want to see the details limited to the 1000 records once I join two table because my Time Dimension contains date which goes till 2050.

Can anyone suggest a better way of joining the two tables?

Thanks,

H

[Oleg_Troyansky]

First, let's get the terminology right... In QlikView terminology, JOINING is the type of LOAD where some fields from one table are "joined" into another table, based on matching identical keys. The end result is a single table with more fields.

In your description, you are loading two separate tables - Fact and Dimension, and your are "ASSOCIATING" those tables by renaming the date field in the Fact table to match the Date field in the Dimension Table.

If you only want to see those Dates that you have Fact data for, you should load your Fact table first, and then load the Dimension table, using WHERE exists() clause. For example:

Fact:

load * from FACT...;

Calendar:

LOAD * from Time_Dimension

WHERE exists(DATE_WID)

;

This way, only relevant dates will get loaded into QlikView. Same approach applies to any Dimensions - Items, Customers, etc...

[pover]

Why do you want to join the revenue fact table and the time dimension?

If you want to limit the resulting table to 1000 records do a left join like the following example:

Load DATE as [DATE WID]
,*
Resident REV_FACT_TABLE;
LEFT JOIN
Load *
Resident TIME_DIM;

This will keep the Revenue Fact Table to 1000 as long as the rows between the 2 tables have a 1-to-1 relation.

Regards.

[pover]

Haha...Oleg, you beat me on that one. We need to coordinate the effort.

Also, you can use a left keep considering both tables just share the DATE_WID column.

Fact:
load * from FACT...;

Calendar:
LEFT KEEP (Fact)
LOAD * from Time_Dimension

[Oleg_Troyansky]

Karl,

you are right, LEFT KEEP will do the same. I think that for truly large tables WHERE EXISTS() should be more cost-effective, but I never tested it...