Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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