JOIN between FACT and DIMENSION Tables

[hkg_qlik]

Hi

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?

REV_FACT: Column Names are as follows:

1. CUSTOMER_NAME
2. CUST_NUMBER
3. REVENUE
4. GL_DATE
5. GL_WID (key)

TIME_DIMENSION: COlumn Names are as follows:

1. ROW_WID (key)
2. YEAR
3. MONTH
4. DAY

What I am doing here is that I am Naming GL_WID AS ROW_WID. And its not working for me.

Please advise.

H

[johnw]

I don't know if I understand what you're asking, but if you only want the Time dimension records that match the revenue fact table, I'd probably skip loading them by using exists():

REV_FACT:
LOAD
CUSTOMER_NAME
,CUST_NUMBER
,REVENUE
,GL_DATE
,GL_WID AS ROW_WID
FROM wherever
;
TIME_DIMENSION:
LOAD
ROW_WID
,YEAR
,MONTH
,DAY
FROM somewhere
WHERE EXISTS(ROW_WID)
;

[hkg_qlik]

Hi John,

I tried to follow your instructions on joining the two table but I am getting the following error:

ErrorMsg: ORA-00928: missing SELECT keyword
SQL SELECT *
FROM ODS."W_XXMSC_DAY_D"
WHERE EXISTS (ROW_WID);

Let me know if I am doing anything wrong.

Thanks

H

[johnw]

Maybe like this, then. The exists() is a QlikView function, so it must be in the QlikView portion (the load) instead of in the SQL portion.

TIME_DIMENSION:
LOAD
ROW_WID
,YEAR
,MONTH
,DAY
WHERE EXISTS(ROW_WID)
;
SQL SELECT *
FROM ODS."W_XXMSC_DAY_D"
;

[hkg_qlik]

Hi John,

I tried using inside the Qlikview portion but now its giving me syntax error.

Thanks,

H

[johnw]

Well, I can't fix it unless you post the code that's getting a syntax error.

[hkg_qlik]

Here is the code for which is causing the syntax error:

TIME_DIMENSION:

LOAD "ROW_WID",
"CALENDAR_DATE",
"CAL_HALF",
"CAL_MONTH",
"CAL_QTR",
"CAL_WEEK",
"CAL_YEAR",
WHERE EXISTS "ROW_WID";
SQL SELECT *
FROM ODS."W_XXMSC_DAY_D";

And I am trying the above TIME_DIMENSION with:

REVENUE_FACT:

LOAD "BRANCH_NAME",
"GL_DATE_WID" AS "ROW_WID";
"CUST_NAME",
"ACCT_NO",
"COST_TYPE",
"ORDER_TYPE",
INVOICE,
"LINE_NUMBER",
"ITEM_NUMBER",
"INV_ORG",
"SHIPPING_BRANCH",
"SALES_ORDER",
"EXT_AMT",
CHARGES;
SQL SELECT *
FROM ODS."W_XXMSC_REV_COGS_F";

Thanks

H

[johnw · Accepted solution]

You have an extra comma after "CAL_YEAR". Also, if you want to only load times that are in your revenue table, you have to load the revenue table first. But perhaps you were just cut and pasting in the opposite order.

[hkg_qlik]

Thanks John.