topic Re: Calendar with Outlets in QlikView

Calendar with Outlets

dominikkeller — Sat, 16 Nov 2024 02:35:19 GMT

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
...	...

Re: Calendar with Outlets

marcus_sommer — Mon, 02 Sep 2019 11:12:43 GMT

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

Re: Calendar with Outlets

dominikkeller — Mon, 02 Sep 2019 11:16:25 GMT

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

Re: Calendar with Outlets

marcus_sommer — Mon, 02 Sep 2019 11:21:32 GMT

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

Re: Calendar with Outlets

Vegar — Mon, 02 Sep 2019 11:28:01 GMT

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;

Re: Calendar with Outlets

Brett_Bleess — Tue, 10 Sep 2019 21:48:33 GMT

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