Calendar with Outlets

[dominikkeller]

Hey Qlik Ninjas,

i need to build a master calendar. But i need to build a key with the date, time and outlet-id. So how can i build a calendar that contains every timestamp for every outlet on my list.

This my calendar so far. 

LET vMinDate = Num(MakeDate(2017,1,1)); 
LET vMaxDate = Floor(MonthEnd(Today(-1))); 

LET vDays = vMaxDate - vMinDate ;


Calendar:

LOAD Year(KeyDate) as Year,

Date(MonthName(KeyDate),'MMM/YYYY') as MonthYear,

Month(KeyDate) as Month,

Day(KeyDate) as Day,

Date(Floor(Date(KeyDate,'MM/DD/YYYY')),'MM/DD/YYYY') as Date,

Hour(KeyDate) AS Hour,

Timestamp(Floor(KeyDate, 1/24),'MM/DD/YYYY HH') as KeyDate;

LOAD

    Timestamp($(vMinDate) + (RecNo()-1)/24) as KeyDate

AUTOGENERATE 24 * $(vDays) + 1;


store Calendar into CalendarTest.qvd(QVD);

And i have list with my Outlets like:

ID	Name
1	Hamburg
...	...

[marcus_sommer]

Including a time-part into a calendar is IMO seldom a good idea. Usually it's better to use a master-calendar and a master timetable - and depending on your data and requirements it may necessary to use multiple ones. What's the aim behind the purpose of linking the calendar with an outlet-id?

- Marcus

[dominikkeller]

Hey Marcus,

at the moment my payment-roll is also my main table in that are dates and times. My Problem now is, i have another fact table with date and timestamps. So i need a link table. In that i need every id, date and time. that's my case

[marcus_sommer]

Mostly it's easier and more performant not to create a association or a link-table between two fact-tables else just to concatenate them.

- Marcus

[Vegar · Accepted solution]

Consider  @marcus_sommer  advice,   but if still you want to replicate your timestamps for all outlets you perform a join it with your outled Id table like I've done below.

Calendar:
LOAD 
  Year(KeyDate) as Year,
  Date(MonthName(KeyDate),'MMM/YYYY') as MonthYear,
  Month(KeyDate) as Month,
  Day(KeyDate) as Day,
  Date(Floor(Date(KeyDate,'MM/DD/YYYY')),'MM/DD/YYYY') as Date,
  Hour(KeyDate) AS Hour,
  Timestamp(Floor(KeyDate, 1/24),'MM/DD/YYYY HH') as KeyDate
;
LOAD
  Timestamp($(vMinDate) + (RecNo()-1)/24) as KeyDate
AUTOGENERATE 
  24 * $(vDays) + 1;

JOIN (Calendar) LOAD 
  ID as OutletId
FROM 
  OutletData;

[Brett_Bleess]

Dominik, just leaving you a couple of Design Blog links that may prove useful in addition to what Marcus and Vegar gave you.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Time-Table/ba-p/1469392

Sorry I am not much more help than that.  If Vegar's info helped you solve things, be sure to give him credit by clicking the Accept as Solution button on his post, that way others know that worked as well.

Regards,
Brett