topic Re: Master Calendar in QlikView

Master Calendar

mszuberl — Thu, 28 Jul 2016 21:57:12 GMT

Hi,

Can someone help me with this issue?

In the attached picture, you can see that not all time tamps in the master calendar have yer, month, etc. associated with them.

Also, although I load DISTINCT, I see duplicates (row 3 and 4).

My calendar script is here:

Master_Calendar_Link_Table:

Load Distinct

ID as key_Calendar,
Timestamp(round("Scan_Completed_DT", (1/24))) as Date

Resident DATATABLE;

MinMaxDates:
LOAD Floor(Min(TimeStamp(Date, 'MM/DD/YYYY hh:mm'))) AS MinDate,
Floor(Max(TimeStamp(Date, 'MM/DD/YYYY hh:mm'))) AS MaxDate
RESIDENT Master_Calendar_Link_Table;

LET vMinDate = FieldValue('MinDate', 1);
LET vMaxDate = FieldValue('MaxDate', 1);

DROP TABLE MinMaxDates;

CalendarTemp:
LOAD
TimeStamp($(vMinDate) + (RecNo()/24) + (IterNo() - 1)) AS AddedTimeStamp
AUTOGENERATE 24 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

MasterCalendar:
LOAD
AddedTimeStamp AS Date,
Year(AddedTimeStamp) AS Year,
month(AddedTimeStamp) AS Month,
day(AddedTimeStamp) AS Day,
hour(AddedTimeStamp) AS Hour,
weekday(AddedTimeStamp) AS Weekday
Resident CalendarTemp;

Drop table CalendarTemp;

Re: Master Calendar

rwunderlich — Thu, 28 Jul 2016 23:49:40 GMT

The timestamps in the LinkTable and the Calendar are not equivalent values. You'll need to apply the same rounding in the CalendarTemp:

TimeStamp(round($(vMinDate) + (RecNo()/24) + (IterNo() - 1), (RecNo()/24)))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar

mszuberl — Fri, 29 Jul 2016 20:04:22 GMT

Rob, thank you very much. Your insight resolved duplicate dates in the master calendar.

However, I still see values where year, month, weekday are blank. Do you know what could be causing these?

(When I break the link between the fact table and the master calendar, master calendar does not have these blanks).

Re: Master Calendar

rwunderlich — Sat, 30 Jul 2016 00:40:31 GMT

Based on your description, it sounds like you are not generating the rows in the master calendar? For example, when you break the link, does a row for 7/12/2015 05:00 appear in the master calendar? Is the Information Density 100% for all fields in the Master Calendar? (hover over the fields in the Table Viewer).

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar

mszuberl — Sat, 30 Jul 2016 03:58:29 GMT

Rob,

When link is broken, that records does not show up in the master calendar.

Marcin

Re: Master Calendar

Anil_Babu_Samineni — Sat, 30 Jul 2016 07:43:13 GMT

Date(Floor(Min(Timestamp#(DateTimeField, 'YYYYMMDD hh:mm:ss'))), 'M/D/YY') Use this instead of Floor(Min(TimeStamp(Date, 'MM/DD/YYYY hh:mm'))) AS MinDate

Re: Master Calendar

mszuberl — Thu, 04 Aug 2016 15:59:49 GMT

Anil,

Were you able to test this potential solution?

I have made the changes but still cannot make this work.

Rob, another interesting observation in my master calendar.

For example: when using Month dimension (from master calendar) in a chart, I see a lot of records with Month blank.

However, when using expression Month(Date) (Date is from Master Calendar), I do not see any blanks.

Do you have any hints on how to fix this? (Using expressions as dimensions will not work, since I have lots of charts and pivot tables).

Marcin

PS. Is there possibly as sample master calendar .qvw, where records are generated both in master calendar (missing data generation) and in the fact table?

Re: Master Calendar

rwunderlich — Wed, 10 Aug 2016 01:43:04 GMT

What is the subset ratios for the Date field in both tables?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar

mszuberl — Thu, 25 Aug 2016 18:51:24 GMT

Rob,

Very sorry for taking this long to respond.

I was out of the office, on paternity leave (joined the "Fathers" club lately).

The subset ratios are:

Link table: 86%

Master Calendar: 49%

Re: Master Calendar

rwunderlich — Sat, 27 Aug 2016 05:49:49 GMT

Congratulations on the new baby!

Your subset ratio in the Master Calendar should be 100%. So it looks like your Date fields still contain different content. Can you post a QVW?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar

mszuberl — Tue, 30 Aug 2016 18:36:04 GMT

Rob, thank you for your congratulations and all help so far!

I may have figured this out, while working on a sample .qvw for you.

Anyways, the .qvw is attached. I believe the master calendar is correct now.

One last question though - as I make selections in the data, not all dimension will have all dates in master calendar associated with them, thus gaps in the time axis will be present.

Is there a way in QV to force showing all values on the time axis, despite not having data for them?

Sincerely,

Marcin

Re: Master Calendar

rwunderlich — Wed, 31 Aug 2016 06:14:23 GMT

Your sample seems to show all Dimensions (you've checked "Show All Values" and unchecked "Suppress Zero Values"). Can you provide a selection scenario and a screenshot of your issue?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com