Issue with mismatching date formats

[alextomlins]

Qlik Sense Gurus Assemble!

Hi guys,

I am having trouble associating on a date field between my master calendar date field and my fact table.

I have got a date time data type but is but the data is aggregated by hour so e.g '31/01/2018 01:00:00:00'

I want to get my master calendar is currently coming out as  ''31/01/2018 01:00:00:11' and so there is no link.

Has anyone got any advice for me pretty please?

the master calendar script is as follows, the date hour is in bold - this is the one I am struggling to get to '31/01/2018 01:00:00:00' ;

Temp: 

Load 

min(SubscribedDateTime) as minDate, 

max(SubscribedDateTime) as maxDate 

Resident Subscriptions; 

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

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

LET vDays = varMaxDate - varMinDate + 2;

MasterCalendar:

LOAD

Date(Floor(DateHour)) AS Date,

DateHour,

  Hour(DateHour) AS Hour,

  Week(DateHour) AS Week,

  Year(DateHour) As Year, 

  Month(DateHour) As Month,

  MonthName(DateHour) As MonthName,

  WeekYear(DateHour) AS [WeekYear],

  Month(DateHour) & '-' & Year(DateHour) as MonthYear, 

  ApplyMap('QuartersMap', month(DateHour), Null()) as Quarter, 

  WeekDay(DateHour) as WeekDay,

  Minute(DateHour) AS Minute;

LOAD

Timestamp(Timestamp($(varMinDate)) + ((RecNo() - 1)/24)) as DateHour

AUTOGENERATE 24 * $(vDays);

DROP Table Temp; 

[ali_hijazi]

if you don't care about time then make the following:

in your master calendar script use num(DayStart(Date(DateHour))) as Date

and in your fact table use the same num(dayStart(Date(fact_table_date_column))) as Date

[alextomlins]

Hi Ali,

Thanks for your help. I seem to have fixed this now. I did care about time but only the hour !

So it's a weird format because it's '31/01/2018 01:00:00:00'

Would people normally do this or would they concatenate a Date and and Hour column to make a key.

I am very new to learning QLik so apologies. I'm just learning how to associate tables in the script at the moment and am having troubles with blasted dates

Thanks,

Alex

[ali_hijazi]

yes I suggest that you link your fact to master calendar via a date value and put the hour in your fact table in case you want to use it as filter