
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar - how to join properly?
I somewhat followed a guide to loading a Master Calendar and modded it to my needs. It ended up like this (variables MinDate1 and MaxDate3 are introduced in a previous section):
Let varMinDate = Num(MinDate1);
Let varMaxDate = Num(MaxDate3);
[TempCalendar]:
Load
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
[MasterCalendar]:
Load
[TempDate] as [MasterDate],
Week(TempDate) as [Week],
Year(TempDate) as [Year],
Month(TempDate) as [Month],
Day(TempDate) as [Day],
Year(TempDate) & '-' & Week(TempDate) as YearWeek,
WeekDay(TempDate) as WeekDay
Resident [TempCalendar]
Order By [TempDate] asc;
Drop Table [TempCalendar]
;
And that's working fine, I get a nice table with a bunch of dates in good order. Then I load a some other tables, renaming their date columns to [MasterDate] so Qlik makes the association. Then I go to sheet and put the MasterDate into a chart, and it looks like this:
What is happening here? Why is it not just showing me one row per date? The other tables have multiple rows for every date, but my master calendar should obviously not be in a one-to-one relationship with them like this. I come from a PowerBI background where I would just tell PowerBI that these tables are linked through a one-to-many relationship upon creating the relationship, but I can't find anything about how to do the same thing in Qlik.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you are seeing the date part of a timestamp. Look at the date fields on the other tables, if they are timestamps, you will need to use a floor command to just get the date part.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you are seeing the date part of a timestamp. Look at the date fields on the other tables, if they are timestamps, you will need to use a floor command to just get the date part.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That was exactly the problem, thank you!
I thought the command Date(datecolumn) would extract just the date, but apparently not. Floor(Date(datecolumn)) did the trick.
