Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
alextomlins
Contributor III
Contributor III

Issue with mismatching date formats

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; 

3 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
alextomlins
Contributor III
Contributor III
Author

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
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes