5 Replies Latest reply: Oct 26, 2015 11:49 AM by Dave Jones RSS

    Qlik Sense - How to connect dates in seperate files with different names

      Hi all,

       

      I am fairly new to Qlik Sense but have managed to put something together that works well - Apart from one aspect - Dates.

       

      I have 2 files - One contains an invoice date whilst another is phone records and the date of the call - This data is joined successfully without any real ID fields but the dates dont work together in visualisations - Clicking week 34 on invoices doesnt filter week 34 on calls

       

      I have is the files contain date fields [Invoice Date] and [Start Date] - I also have a master calendar for both these fields that fills in any dates missing.

       

      Does anyone know how to create a master calendar to pull InvoiceWeek & CallWeek together etc ?

       

      I have looked at

      • master tables (Unable to see how to join 2 existing master calendars but know how to create one from a single data set)
      • Canonical Date - I cant see how to do it without a key or ID as the glue

       

      Any help would be much appreciated.

       

      Thanks

       

      Dave

        • Re: Qlik Sense - How to connect dates in seperate files with different names
          Robert Mika

          Post your data or application so we can look into this.

          • Re: Qlik Sense - How to connect dates in seperate files with different names
            Robert Hutchings

            "This data is joined successfully without any real ID fields but the dates don't work together in visualisations"

             

            How did you do this? Because surely you need common link fields in the call and invoice tables (files) to join them. And if you have this it should then be easy enough to create a canonical date. Without it ...

            • Re: Qlik Sense - How to connect dates in seperate files with different names
              Teck Rul Saw

              perhaps, in the load editor?

               

              Load

              ....,

              Invoice_Date as dateSet
              from Invoice

               

              Load

              ....,

              Call_Date as dateSet

              from phone_record

               

              Would this fulfill? Sry if i misunderstood.

              • Re: Qlik Sense - How to connect dates in seperate files with different names
                jagan mohan rao appala

                Hi Dave,

                 

                How you join those two dates? Those two are different fields you should not join both this dates, try creating two Master Calendars one for Invoice & one for Start date, based on your requirement you display this fields.

                 

                Or you can try Canonical Date by HIC for single calendar table.  If you can attach sample data and your expected results then it would be easier to provide effective solution.

                 

                Regards,

                Jagan.

                • Re: Qlik Sense - How to connect dates in seperate files with different names

                  Thanks all,

                   

                  I will try to explain how it works and have added my code below:

                   

                  • The [Driver calls], [Start date] is the date of the incoming phone call along with the drivers phone number. This is matched in a table called [Driver Data] that links the incoming phone number with a real driver name.
                  • TRNLIST contains transaction data along with the [Driver number] from the [Driver Data] file

                   

                  From this info i have produced a graph of invoices per day relevent to the Drivers and calls relevent to the drivers.

                   

                  If i click the visualisation the graph data doesnt filter for the same timeframe for calls and invoicesCapture.PNG

                   

                  I am not sure whether i should be loading both [INVOICE DATE] and [Start date] as MasterDate ?

                   

                  [Driver Data]:

                  LOAD [Area Manager],

                    [Division],

                    [Area Manager ID],

                    [Assigned Area],

                    [Driver Name],

                    [Driver number] AS [Driver number],

                    [Driver Mobile Number] AS [Driver MOBILE NUMBER]

                  FROM [lib://Driver Reports/Drivers.xlsx]

                  (ooxml, embedded labels, table is [Driver Data]);

                   

                   

                  [CURRENT]:

                  LOAD [ORDER] AS [Driver number],

                    [QUANTITY],

                    [PRICE]

                  FROM [lib://Driver Reports/CURRENT.xlsx]

                  (ooxml, embedded labels, table is CURRENT);

                   

                   

                  [Driver Calls]:

                  LOAD Date#("Start date",'DD/MM/YYYY') AS [Date Test],

                    [Calling number] AS [Driver MOBILE NUMBER],

                    [Called number] AS [CALLED PHONE NUMBER],

                    [Total seconds] AS [PHONE CALL DURATION]

                  FROM [lib://Driver Reports/Driver Calls.csv]

                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                   

                   

                  [TRNLIST]:

                  LOAD [CUSTOMER] AS [ACCOUNT NUMBER],

                    [REGNUM] AS [REG NUMBER],

                    [INVNUM] AS [INVOICE NUMBER],

                    [OLDADVNUM] AS [JOB CARD NUMBER],

                    [INVDATE] AS [Date Test],

                    [ORDNUM] AS [Driver number],

                    [TSTART] AS [INVOICE VALUE]

                  FROM [lib://Cameo Data Files/TRNLIST.xlsx]

                  (ooxml, embedded labels, table is TRNLIST);

                   

                   

                  [TRNSTK]:

                  LOAD [INVNUM] AS [INVOICE NUMBER],

                    [STCODE] AS [STOCK CODE],

                    [MANUFCTR] AS [MANUFACTURER],

                    [STKTYPE] AS [STOCK TYPE],

                    [QUANTITY] AS [product QUANTITY]

                  FROM [lib://Cameo Data Files/TRNSTK.xlsx]

                  (ooxml, embedded labels, table is TRNSTK);

                   

                   

                  [product Manufacturers]:

                  LOAD [MANUFACTURER],

                    [MANUFACTURER DESCRIPTION],

                    [MANUFACTURER RANGE]

                  FROM [lib://product Manufacturer Data/Brands.xlsx]

                  (ooxml, embedded labels, table is [product Manufacturers]);

                   

                   

                  [Friendly Number Names]:

                  LOAD [CALLED PHONE NUMBER],

                    [CALLED PHONE NUMBER NAME]

                  FROM [lib://Driver Reports/Friendly Number Names.xlsx]

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

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  QuartersMap: 

                  MAPPING LOAD  

                  rowno() as Month, 

                  'Q' & Ceil (rowno()/3) as Quarter 

                  AUTOGENERATE (12); 

                   

                   

                   

                  //InvoiceDateCalendar

                  Temp: 

                  Load 

                                 min([INVOICE DATE]) as minDate, 

                                 max([INVOICE DATE]) as maxDate 

                  Resident [TRNLIST]; 

                   

                  Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                  DROP Table Temp; 

                   

                  TempCalendar: 

                  LOAD 

                                 $(varMinDate) + Iterno()-1 As Num, 

                                 Date($(varMinDate) + IterNo() - 1) as TempDate 

                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                   

                  InvoiceCalendar: 

                  Load 

                                 TempDate AS [INVOICE DATE], 

                                 week(TempDate) As InvoiceWeek, 

                                 Year(TempDate) As InvoiceYear, 

                                 Month(TempDate) As InvoiceMonth, 

                                 Day(TempDate) As InvoiceDay, 

                                 YeartoDate(TempDate)*-1 as InvoiceCurYTDFlag, 

                                 YeartoDate(TempDate,-1)*-1 as InvoiceLastYTDFlag, 

                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as InvoiceRC12, 

                                 date(monthstart(TempDate), 'MMM-YYYY') as InvoiceMonthYear, 

                                 ApplyMap('QuartersMap', month(TempDate), Null()) as InvoiceQuarter, 

                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as InvoiceWeekYear, 

                                 WeekDay(TempDate) as InvoiceWeekDay 

                  Resident TempCalendar 

                  Order By TempDate ASC; 

                  Drop Table TempCalendar; 

                   

                   

                   

                   

                   

                   

                  //CallDateCalendar

                  Temp: 

                  Load 

                                 min([Start date]) as minDate, 

                                 max([Start date]) as maxDate 

                  Resident [Driver Calls]; 

                   

                  Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                  DROP Table Temp; 

                   

                  TempCalendar: 

                  LOAD 

                                 $(varMinDate) + Iterno()-1 As Num, 

                                 Date($(varMinDate) + IterNo() - 1) as TempDate 

                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                   

                  CallCalendar: 

                  Load 

                                 TempDate AS [Start date], 

                                 week(TempDate) As CallWeek, 

                                 Year(TempDate) As CallYear, 

                                 Month(TempDate) As CallMonth, 

                                 Day(TempDate) As CallDay, 

                                 YeartoDate(TempDate)*-1 as CallCurYTDFlag, 

                                 YeartoDate(TempDate,-1)*-1 as CallLastYTDFlag, 

                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as CallRC12, 

                                 date(monthstart(TempDate), 'MMM-YYYY') as CallMonthYear, 

                                 ApplyMap('QuartersMap', month(TempDate), Null()) as CallQuarter, 

                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CallWeekYear, 

                                 WeekDay(TempDate) as CallWeekDay 

                  Resident TempCalendar 

                  Order By TempDate ASC; 

                  Drop Table TempCalendar;