Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

Calendar with Outlets

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:

IDName
1Hamburg
......
Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Calendar with Outlets

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;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Calendar with Outlets

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

Highlighted
New Contributor III

Re: Calendar with Outlets

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Calendar with Outlets

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

Highlighted
Partner
Partner

Re: Calendar with Outlets

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;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Digital Support
Digital Support

Re: Calendar with Outlets

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.