How to create Master Calendar with Multiple date fields with in the same table and among the tables

narayanaqlik — Sun, 05 Jan 2020 03:23:34 GMT

Hi All, I Have nearly 40 tables , in each table has contain 2, 3 dates like some table Created Date, Updated Date, and some tables Created Date, Updated Date,Due Date, Completed Date. And all the tables have their mapping based on the Key fields Here the date fields has contain some missing dates , i need to make them continuous dates Here my question is how to create a master calendar and bring the all the dates and map to that master calendar Thanks for hope help in this area with good suggestions or logic

Re: How to create Master Calendar with Multiple date fields with in the same table and among the tab

stevedark — Sun, 05 Jan 2020 15:41:07 GMT

Hi,

Personally, I don't tend to have calendar tables in separate tables in my data model, rather create various date parts in each table, like this:

LOAD
[From Date Time],
Date(DayStart([From Date Time]), 'DD MMM YYYY') as [From Date],
Year([From Date Time]) as [From Year],
Month([From Date Time]) as [From Month Name],
Date(MonthStart([From Date Time]), 'MMM-YY') as [From Month],
[To Date Time],
Date(DayStart([To Date Time]), 'DD MMM YYYY') as [To Date],
Year([To Date Time]) as [To Year],
Month([To Date Time]) as [To Month Name],
Date(MonthStart([To Date Time]), 'MMM-YY') as [To Month],

I wrote a blog post on why I prefer this approach.

This doesn't deal with the problem of gaps in dates though. In some ways, having the gaps helps see where there are missing data, and I would therefore say is a good thing.

You could create a calendar QVD and load from it many times, once for each date, e.g.

FromCalendar:
LOAD
Date as [From Date],
Month as [From Month],
Year as [From Year],
[Month Name] as [From Month Name]
FROM [lib://qvd/Calendar.qvd] (qvd)
;

If you don't want redundant dates at the start and end of each date table you will need to do the max min on each field and use this in a WHERE statement on the QVD. This processing can be slow though.

A better approach may be to create an events table and build the calendar on this. This will give you a single calendar and you can then pick what type of date you are looking at. To do this, first load your MainData table and ensure it has a Unique Key. Then load into an Events table for each date in the table:

Events:
LOAD
1 as DateCount,
UniqueKey,
'From Date' as DateType,
[From Date] as Date
RESIDENT MainData;

Events:
LOAD
1 as DateCount,
UniqueKey,
'To Date' as DateType,
[To Date] as Date
RESIDENT MainData;

You can then hang your calendar table off of the Date field or build it into the table.

This is particularly nice if you have things like support tickets to track, as you can have a line chart with the sum of DateCount on it and a dimension of DateType, then each line would be for things like Tickets Opened, Tickets Closed, Tickets Suspended etc..

As all of these techniques involve doing the same thing many times (once for each date you have) you may want to look into using variables with parameters in them, but these can get a bit fiddly.

Hopefully that points you in the right direction.

Steve

topic Re: How to create Master Calendar with Multiple date fields with in the same table and among the tab in App Development

How to create Master Calendar with Multiple date fields with in the same table and among the tables

Re: How to create Master Calendar with Multiple date fields with in the same table and among the tab