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