

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
JOIN between FACT and DIMENSION Tables
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
- Tags:
- joins
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi John,
I tried using inside the Qlikview portion but now its giving me syntax error.
Thanks,
H


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Well, I can't fix it unless you post the code that's getting a syntax error.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks John.
