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.
(ooxml, embedded labels, table is Elavon);
[Site_ID] AS [FX1254_itemisation ToDate.Site_ID],
[Site Name] AS [Site Name-Sitename],
[Month] AS [FX1254_itemisation ToDate.Month],
[Date] AS [FX1254_itemisation ToDate.Date],
[Description] AS [FX1254_itemisation ToDate.Description],
(ooxml, embedded labels, table is [FX1254_itemisation ToDate]);
(ooxml, embedded labels, table is GP_IC_Rates);
[Description] AS [GP_Scheme_Fees.Description],
(ooxml, embedded labels, table is GP_Scheme_Fees);
[Date] AS [HH.Date],
FROM [lib://qlikid_djbrennan/HH Elec Faked for Busaba.xlsx]
(ooxml, embedded labels, table is HH);
[Sitename] AS [Site Name-Sitename],
(ooxml, embedded labels, table is Sites);
(ooxml, embedded labels, table is Sheet1);
[Month] AS [All Statements.Month],
(ooxml, embedded labels, table is [All Statements]);
[Month] AS [mob_detail.Month],
[Description] AS [mob_detail.Description],
FROM [lib://qlikid_djbrennan/O2_ Inv Line Detail YTD.xlsx]
(ooxml, embedded labels, table is mob_detail);
// what follows is a mistake - blank columns
(ooxml, embedded labels, table is [Number Detail]);
// Below is Rob Wunderlich’s script for unrelated multi-fact tables and common date
// for some reason the name “DateLink” as used by RW didn’t work.
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
Card_Type //Fact Key - for each table?
, [St_Date] as Date //Fact Date
, ‘Cards’ as DateType //Fact Type
RESIDENT [All Statements]
Mob_Num //Fact Key - for each table?
, [Invoice Date] as Date //Fact Date
, ‘Mob’ as DateType //Fact Type
// Call Calendar Generator for field “Date”
// Any calendar may be used as long as it’s linked to the field “Date”
Re: Confusion around Calendar and Dates in QLik Sense
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"?