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

[#1]

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

[robert_mika]

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

[robert99]

"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 ...

[#4]

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.

[jagan]

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.

[#6]

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 invoices

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;