Hello,
I'm trying to put together two different fact set tables, one that houses the sales data and one that houses the tours data. Each Sale has to have a tour, but not all tours result in sales. It's not as simple as an outer join. We can pull our data with date parameters. However, people often tour at different dates then the sale. So, I'm often left with two fact tables that may or may not connect to one another.
What I want to be able to do is select date parameters (let's say 10/01/2015 - 12/01/2015) and have the information displayed that pulls in both sales that were sold during that time frame, and tours that were done during that time frame. The idea is to calculate sales per tour for a given time frame.
The problem I encounter at the moment is around the date fields.
Here is the basic structure of the tables:
Sales Table:
///[Project Code],
///[Project Description],
[Project Type Description],
///[Contract Project Number (Formatted)],
///[Unit Type],
///[Unit Phase],
///[Season Number],
///[OEB Code],
[Sales Status],
///[Office Code],
///[Office Site],
[Office Sales Line],
[Office SubSite],
//[Sales Date],
[Contract Number],
[Gross Volume],
[Cancel Volume]
Tours Table:
[Tour ID],
///[Office Site],
///[Office Code],
//[Tour Date],
[Tour Status],
///[Project Code],
Qualified,
///[Contract Project Number (Formatted)],
///[Unit Type],
///[Unit Phase],
///[Season Number],
///[OEB Code],
///[Project Description]
Here is what I've put together for code so far:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
LOAD ///[Project Code],
///[Project Description],
[Project Type Description],
///[Contract Project Number (Formatted)],
///[Unit Type],
///[Unit Phase],
///[Season Number],
///[OEB Code],
[Sales Status],
///[Office Code],
///[Office Site],
[Office Sales Line],
[Office SubSite],
//[Sales Date],
[Contract Number],
[Gross Volume],
[Cancel Volume],
[Project Code]&'_'&[Office Site]&'_'&[Contract Project Number (Formatted)]&'_'&[Office Code]&'_'&[Project Description]&'_'&[Unit Type]&'_'&[Unit Phase]&'_'&[Season Number]&'_'&[OEB Code]&'_'&[Sales Date] as Key
FROM
(ooxml, embedded labels, table is Sales_1);
LOAD [Tour ID],
///[Office Site],
///[Office Code],
//[Tour Date],
[Tour Status],
///[Project Code],
Qualified,
///[Contract Project Number (Formatted)],
///[Unit Type],
///[Unit Phase],
///[Season Number],
///[OEB Code],
///[Project Description]
[Project Code]&'_'&[Office Site]&'_'&[Contract Project Number (Formatted)]&'_'&[Office Code]&'_'&[Project Description]&'_'&[Unit Type]&'_'&[Unit Phase]&'_'&[Season Number]&'_'&[OEB Code]&'_'&[Tour Date] as Key
FROM
(ooxml, embedded labels, table is Tours_2);
LinkTable:
LOAD Distinct
[Project Code]&'_'&[Office Site]&'_'&[Contract Project Number (Formatted)]&'_'&[Office Code]&'_'&[Project Description]&'_'&[Unit Type]&'_'&[Unit Phase]&'_'&[Season Number]&'_'&[OEB Code]&'_'&[Tour Date] As Key,
[Project Code],
[Office Site],
[Contract Project Number (Formatted)],
[Office Code],
[Project Description],
[Unit Type],
[Unit Phase],
[Season Number],
[OEB Code],
[Tour Date] As Date
From
(ooxml, embedded labels, table is Tours_2);
Linktable:
Load Distinct
[Project Code]&'_'&[Office Site]&'_'&[Contract Project Number (Formatted)]&'_'&[Office Code]&'_'&[Project Description]&'_'&[Unit Type]&'_'&[Unit Phase]&'_'&[Season Number]&'_'&[OEB Code]&'_'&[Sales Date] As Key,
[Project Code],
[Office Site],
[Contract Project Number (Formatted)],
[Office Code],
[Project Description],
[Unit Type],
[Unit Phase],
[Season Number],
[OEB Code],
[Sales Date] As Date
FROM
(ooxml, embedded labels, table is Sales_1);
