2 Replies Latest reply: Jan 13, 2017 8:11 AM by Petter Skjolden RSS

    Multiple fact tables and common dates

    denis Brennan

      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

        [E],

        [F],

        [G],

        [H],

        [I],

        [J]

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