Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to create a time dimension and link to master calender. I'm welcome your ideas.
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.
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.
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.
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;
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.
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;
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?
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...)
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.
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.