topic Re: How to create time dimension and link to master calender in QlikView

How to create time dimension and link to master calender

Thu, 27 Sep 2012 12:26:58 GMT

I would like to create a time dimension and link to master calender. I'm welcome your ideas.

Re: How to create time dimension and link to master calender

swuehl — Thu, 27 Sep 2012 14:28:48 GMT

There are tons of threads here in the forum that discusses the concept of a master calendar, also lots of sample app to create such a master calendar.

For example

http://community.qlik.com/qlikviews/1001

but just search the forum 'master calendar' for more.

So, the only problem should be to link your field to the master calendar. QV will link fields with same name.

Just take care that an actual link will only happen to records in the two tables where key field values are equal.

Re: How to create time dimension and link to master calender

Thu, 27 Sep 2012 15:37:15 GMT

You are correct, there are lot of dicussions are around Master Calender but not on time dimension and link the time dimension to master calender.

Thanks.

Re: How to create time dimension and link to master calender

swuehl — Thu, 27 Sep 2012 15:53:01 GMT

As said, links will be created by equality of field values. So, to succesfully link a time dimension to a master calendar that is based on dates, you need to strip of your time part from your time dimension:

LOAD

DATETIME,

Daystart(DATETIME) as DATE

FROM YOURTABLE;

LOAD

DATE,

MONTH,

YEAR

FROM MASTERCAL;

Re: How to create time dimension and link to master calender

Tue, 02 Oct 2012 13:55:27 GMT

I did not understand where are you linking your master calender to time dimention. I have successfully created master calender but I'm having difficulty in create a time dimension and link to master calender.

Thanks.

Re: How to create time dimension and link to master calender

Tue, 02 Oct 2012 14:13:56 GMT

Use this...

Date:
LOAD
today()-recno()+1 as Date
autogenerate(today()-'2009-01-01'); //you can choose here how far you wish to go back by setting appropriate date

DateParts:
LOAD
Date as invoice_date,
Date as Date,
Year(Date) as Year,
yearname (Date, 0, 4 ) as FinancialYear,

Month(Date) as Month,
Day(Date) as Day,
Week(Date) as Week,
Weekday(Date) as WeekDay,
MonthName(Date) as monthYear
RESIDENT Date;

Re: How to create time dimension and link to master calender

swuehl — Tue, 02 Oct 2012 14:14:11 GMT

The link is established by DATE field. Probably I am still missing your issue. Could you post your script code or a small sample app?

Re: How to create time dimension and link to master calender

Tue, 02 Oct 2012 14:42:43 GMT

Hi PurnaC10,

I also like an example app that illustrates your problem, and/or the description of the problem you're solving.

I can imagine you just need a time dimension next to your mastercalendar. In that way the Date dimension (mastercalendar) and your timedimension (new dimension) are not related to eachother but they are both related to your fact-table. This would require a time-field in your facttable that links to the time-field in your time-dimension.

In case the mastercalendar and time-dimension must be related, I think your mastercalendar will have quite a lot of records (depending on the granularity). What is your granularity? Is data viewed per hour? Per minute? Per second?

--> There may be a good reason for it... Lets have a look at your example app or further description

#edit: I saw this topic (http://community.qlik.com/thread/24869) with a clear example with timestamp included in the mastercalendar (so apparently it is not that strange to do...)

Re: How to create time dimension and link to master calender

Thu, 04 Oct 2012 16:29:24 GMT

here is my application and I have been trying to achieve

1. Create Master Calender and Tie with Time with granularity of 15 min interval.

Year, Quarter, Month, Week, Day, Hour, Min (15,30,45,60)

I'm seeing a link between the tables with link table. but some how location and master calender values are not linking on sheet. If I select location both charts (Mech Stats and DB Sessions) are showing valus. then if I select date or month or quarter or year, selection box not keeping multiple selection.

I would like to know why data not connectiong as shown in table layout.

Thanks for your help.

Re: How to create time dimension and link to master calender

swuehl — Thu, 04 Oct 2012 16:40:10 GMT

I think you are trying to link a field with text values only (DTTM field in table LinkTable) with a field with a QV timestamp (DTTM field in table Calendar). I believe QV will try to match the numerical value of the timestamp with the text value, which will not work.

Try using same format in both fields.

Re: How to create time dimension and link to master calender

Thu, 04 Oct 2012 17:52:47 GMT

I dont think, if you look all the places I'm using MM/DD/YYYY HH:MI format. Second thing whey Location data has not link with caledner.

Thanks for your time.

Re: How to create time dimension and link to master calender

Fri, 05 Oct 2012 10:08:55 GMT

I think swuehl is right: the link between the calendar and linktable is not right because the field DTTM is interpreted as text, and they have a different format (one with seconds and one without).

I hope the attached ZIP file helps you.

When you extract the 2 .qvw's in the same folder, the reload will work (I'm using binary load of the original qvw).

In newVersion.qvw you can see that it is needed to "parse" the DTTM field (with timestamp#() and timestamp() functions.

I saw that this was needed, because in the old .qvw, it is visible that those fields were NOT timestamps. In the new .qvw they are (see the screenshot in the zip-file: it has a tag like timestamp)

This file helps you to make the link from calender to the linktable.

I assume you can make the other changes yourself to tweak the calendar (I think now for every minute there is a record instead of per 15 minutes)

Please let me know if this helped.

Re: How to create time dimension and link to master calender

Sun, 07 Oct 2012 01:57:58 GMT

Thank you guys for your help. Its good information to know.

Re: How to create time dimension and link to master calender

Anonymous — Mon, 14 Jan 2013 10:46:24 GMT

Hi All,

I am using the below script for my master calender. I have date field name as '[Settled Date]' in my data source.

But i am getting the error

(Script line error:

DataField:

LOAD

date (+ rowno()-1) as TempDate

AUTOGENERATE

-+1).

Can any one help me?

// **************** Creating the Calendar *************

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));

LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));

LET varToday =num(today());

// **************** Creating the Calendar Comment*************

DateField:

LOAD

date ($(varMinDate) + rowno() -1) as TempDate

AUTOGENERATE

$(varMaxDate)- $(varMinDate)+1;

// ****************Creating the Master Calendar************

MasterCalendar:

LOAD

TempDate as OrderDate,

Day(TempDate) as Day,

WeekDay (TempDate) as Weekday,

Week(TempDate) as Week,

Month(TempDate) as Month,

Year(TempDate) as Year,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Date (monthstart (TempDate) , 'MMM_YYYY') as MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

RESIDENT DateField

ORDER BY TempDate ASC;

DROP TABLE DateField;

Re: How to create time dimension and link to master calender

Mon, 14 Jan 2013 11:42:42 GMT

Considered your error-message, I think the cause is that $(varMinDate) is not (correctly) evaluated as a date (but as a null or empty string instead). So the line below that I copied from your code is incorrect.

date ($(varMinDate) + rowno() -1) as TempDate

That varMinDate is filled with this line of code:

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));

question 1: does your Orders table have a valid OrderDate column?

question 2: (if answer on question 1 is 'yes'): after reload (or loading the let varMinDate) please check in the variables overview the value of varMinDate... It should be a valid date...

#Edit: since you're asking a new question, I suggest you open a new thread and refer to this discussion (with a link or something). This question is answered already. When you've done that, you could paste the link of that new topic here...

Re: How to create time dimension and link to master calender

Anonymous — Tue, 15 Jan 2013 09:53:18 GMT

Hi Roberto,

I have posted it in a sep que as "Error in aster Calender Script"

Que1:Its not order table. Its "Settled Date" table and this has the dates

Regards,

Nandha