Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple Fact Set Link Table Help

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);

4 Replies
amayuresh
Creator III
Creator III

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

Concatenate vs Link Table

engishfaque
Specialist III
Specialist III

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:

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

  [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

Not applicable
Author

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?

engishfaque
Specialist III
Specialist III

Dear Jordan,

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

Kind regards,

Ishfaque Ahmed