Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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"?
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: