Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

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
Honored Contributor

Re: Issue with mismatching date formats

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
New Contributor II

Re: Issue with mismatching date formats

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
Honored Contributor

Re: Issue with mismatching date formats

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

Community Browser