topic Multiple Fact Set Link Table Help in QlikView

Multiple Fact Set Link Table Help

Wed, 25 Nov 2020 16:16:04 GMT

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]

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 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 Date

FROM

(ooxml, embedded labels, table is Sales_1);

Re: Multiple Fact Set Link Table Help

amayuresh — Wed, 16 Mar 2016 04:25:51 GMT

You may visit below link to get more information on Link Table and Concatenate

Concatenate vs Link Table

Re: Multiple Fact Set Link Table Help

engishfaque — Wed, 16 Mar 2016 04:45:56 GMT

Dear Jordan,

Here is the script with link table / bridge table. For reference, kindly find attached App.

table1:

LOAD [Project Code]&'_'&[Office Site]&'_'&[Contract Project Number (Formatted)]&'_'&[Office Code]&'_'&[Project Description]&'_'&[Unit Type]&'_'&[Unit Phase]&'_'&[Season Number]&'_'&[OEB Code]&'_'&[Sales Date] as KeyField,

[Project Type Description],

[Sales Status],

[Office Sales Line],

[Office SubSite],

[Contract Number],

[Gross Volume],

[Cancel Volume]

FROM

(ooxml, embedded labels, table is Sales_1);

table2:

[Tour ID],

[Tour Status],

Qualified

FROM

(ooxml, embedded labels, table is Tours_2);

LinkTable:

LOAD Distinct KeyField,

[Project Code],

[Office Site],

[Contract Project Number (Formatted)],

[Office Code],

[Project Description],

[Unit Type],

[Unit Phase],

[Season Number],

[OEB Code],

[Tour Date]

Resident table1;

Concatenate (LinkTable)

LinkTable:

LOAD Distinct KeyField,

[Project Code],

[Office Site],

[Contract Project Number (Formatted)],

[Office Code],

[Project Description],

[Unit Type],

[Unit Phase],

[Season Number],

[OEB Code],

[Tour Date]

Resident table2;

DROP Fields

[Project Code],

[Office Site],

[Contract Project Number (Formatted)],

[Office Code],

[Project Description],

[Unit Type],

[Unit Phase],

[Season Number],

[OEB Code],

[Tour Date]

From table1;

DROP Fields

[Project Code],

[Office Site],

[Contract Project Number (Formatted)],

[Office Code],

[Project Description],

[Unit Type],

[Unit Phase],

[Season Number],

[OEB Code],

[Tour Date]

From table2;

Kind regards,

Ishfaque Ahmed

Re: Multiple Fact Set Link Table Help

Wed, 16 Mar 2016 12:44:58 GMT

Doesn't this omit dates in between where the values don't exist? Wouldn't the creation of a Master Calendar table be of better use or no?

Re: Multiple Fact Set Link Table Help

engishfaque — Thu, 17 Mar 2016 03:02:20 GMT

Dear Jordan,

Master Calendar is better option to hold all the fields regarding dates. Such as, Month, Quarter, Year etc.

Kind regards,

Ishfaque Ahmed