Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Multiple fact tables and common dates

Hi all,

I have been in touch with Rob Wunderlich over at Cookbook. I have tried to use his model as a way to load a common date from multiple and unrelated fact tables. It failed. Rob advised me to post the problem and my Data Load script here. I’de be very grateful for any help or advice anyone can suggest. As is presumably obvious, I’m using Qlik Sense Cloud. The annotated load script follows.

[Elavon]:

LOAD [Card_Type],

  [Elavon_Rate]

FROM [lib://qlikid_djbrennan/EV_Rates.xlsx]

(ooxml, embedded labels, table is Elavon);

[FX1254_itemisation ToDate]:

LOAD

  [Site_ID] AS [FX1254_itemisation ToDate.Site_ID],

  [Site Name] AS [Site Name-Sitename],

  [Telephone_No],

  [Month] AS [FX1254_itemisation ToDate.Month],

  [YearMonth],

  [Date] AS [FX1254_itemisation ToDate.Date],

  [Time],

  [Dialled_No],

  [Description] AS [FX1254_itemisation ToDate.Description],

  [Dur_secs],

  [Cost (£)],

  [Call_Type],

  [PPM],

  [Focus_Contract],

  [Opt_PPM],

  [Opt_Cost],

  [Saving]

FROM [lib://qlikid_djbrennan/fixed_itemisation.xlsx]

(ooxml, embedded labels, table is [FX1254_itemisation ToDate]);

[GP_IC_Rates]:

LOAD [Card_Type],

  [GP_blended_IC Rate]

FROM [lib://qlikid_djbrennan/GP_IC.xlsx]

(ooxml, embedded labels, table is GP_IC_Rates);

[GP_Scheme_Fees]:

LOAD

  [Card_Type],

  [Description] AS [GP_Scheme_Fees.Description],

  [GP_Scheme_Rate],

  [GP_Scheme_pence]

FROM [lib://qlikid_djbrennan/GP_Scheme.xlsx]

(ooxml, embedded labels, table is GP_Scheme_Fees);

[HH]:

LOAD

  [MPANID],

  [Date] AS [HH.Date],

  [TimeSlot],

  [Value],

  [DayNight],

  [WDWE]

FROM [lib://qlikid_djbrennan/HH Elec Faked for Busaba.xlsx]

(ooxml, embedded labels, table is HH);

[Sites]:

LOAD

  [Site_ID],

  [MID],

  [Sitename] AS [Site Name-Sitename],

  [Add_1],

  [Add_2],

  [Postcode],

  [Latitude],

  [Longitude]

FROM [lib://qlikid_djbrennan/Sites.xlsx]

(ooxml, embedded labels, table is Sites);

[Sheet1]:

LOAD [MPANID],

  [Company],

  [Site_ID],

  [Supply],

  [ASC/KVA],

  [Top line],

  [Contract Start],

  [Contract End],

  [StCharge],

  [DayRate],

  [NightRate],

  [DUOSCh],

  [FIT]

FROM [lib://qlikid_djbrennan/Site_elec_Dim.xlsx]

(ooxml, embedded labels, table is Sheet1);

[All Statements]:

LOAD

  [Site_ID],

  [Merchant ID],

  [Site],

  [St_Date],

  [Month] AS [All Statements.Month],

  [Card_Type],

  [Auth],

  [Turnover],

  [Transactions],

  [Rate],

  [MSC Fee]

FROM [lib://qlikid_djbrennan/MC_cum_statements.xlsx]

(ooxml, embedded labels, table is [All Statements]);

[mob_detail]:

LOAD

  [Invoice],

  [Invoice Date],

  [Month] AS [mob_detail.Month],

  [Account number],

  [Mob_Num],

  [Nob_],

  [Description] AS [mob_detail.Description],

  [Nature],

  [Net_Charge],

  [Gross _Charge]

FROM [lib://qlikid_djbrennan/O2_ Inv Line Detail YTD.xlsx]

(ooxml, embedded labels, table is mob_detail);

[Number Detail]:

LOAD [Mob_Num],

  [User],

  [Site_ID],

    // what follows is a mistake - blank columns

  ,

  ,

  ,

  ,

  ,

 

FROM [lib://qlikid_djbrennan/Mob_Users.xlsx]

(ooxml, embedded labels, table is [Number Detail]);

// Below is Rob Wunderlich’s script for unrelated multi-fact tables and common date

LinkTable:       

// for some reason the name “DateLink” as used by RW didn’t work.

LOAD

  MPANID                                                   //Fact Key - for each table?

  , [HH.Date] as Date                                    //Fact Date

  , 'Elec’ as DateType                               //Fact Type

// beyond this point the script is left colured green

RESIDENT [HH]

;

LOAD

  Card_Type                                                   //Fact Key - for each table?

  , [St_Date] as Date                                    //Fact Date

  , ‘Cards’ as DateType                               //Fact Type

RESIDENT [All Statements]

;

LOAD

  Mob_Num                                              //Fact Key - for each table?

  , [Invoice Date] as Date                          //Fact Date

  , ‘Mob’ as DateType                               //Fact Type

RESIDENT [mob_detail]

;

// Call Calendar Generator for field “Date”

// Any calendar may be used as long as it’s linked to the field “Date”

// so see my CommonCalendar script at bottom

CALL CalendarFromField (‘Date’, ‘CommonCalendar’, ‘‘);

// This master calendar script provides the calendar for the Rob Wunderlich code above for multiple fact dates

CommonCalendar:

Load

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

  

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue(Date, recno()))-1 as mindate,

max(FieldValue(Date, recno())) as maxdate

AUTOGENERATE FieldValueCount(Date);

2 Replies
Not applicable

Re: Confusion around Calendar and Dates in QLik Sense

Hi Guys,

It occurs to me that I may not have clearly explained my problem.

I’m trying to build an app for a multi-site business where one sheet will show high-level data for each of several categories. I want to be able to filter that data by date (week, month, quarter, year…).

The fact tables have no relationship between them other than SITE_ID but each fact table has a date.

Example: Each site has electricity consumption data, telephone usage and merchant services fees. The categories don’t matter but the fact that they are logically unrelated is key.

The attached screenshot from Qliksense shows that the data is being correctly returned.

However, I need to be able to filter on the dates and at that point the model fails because I don’t have a “common date”. How best to achieve that "common date"?

Screen Shot 2017-01-05 at 16.06.59.png

MVP
MVP

Re: Multiple fact tables and common dates

This is a must-read article for anyone struggling with dates and multiple dates:

Canonical Date‌ and Why You sometimes should Load a Master Table several times

There is a overview page linking to these and other material on Community here:

How to use - Master-Calendar and Date-Values