topic how to create master calender link with bi-hourly time in QlikView

how to create master calender link with bi-hourly time

Sat, 25 Oct 2014 18:22:26 GMT

I have a date time field with bi-hourly time, i would like to know how to create master calender and link it to bi-hourly time. Here is my data

Date	cpu-0	cpu-1	cpu-2	cpu-3
10/1/2014 1:00	1.22E+01	1.19E+01	0.00E+00	0.00E+00
10/1/2014 3:00	1.04E+01	1.03E+01	0.00E+00	0.00E+00
10/1/2014 5:00	1.68E+01	1.64E+01	0.00E+00	0.00E+00
10/1/2014 7:00	2.33E+01	2.30E+01	0.00E+00	0.00E+00
10/1/2014 9:00	2.45E+01	2.42E+01	0.00E+00	0.00E+00
10/1/2014 11:00	2.59E+01	2.60E+01	0.00E+00	0.00E+00
10/1/2014 13:00	2.66E+01	2.66E+01	0.00E+00	0.00E+00
10/1/2014 15:00	2.76E+01	2.79E+01	0.00E+00	0.00E+00
10/1/2014 17:00	2.69E+01	2.72E+01	0.00E+00	0.00E+00
10/1/2014 19:00	2.55E+01	2.54E+01	0.00E+00	0.00E+00
10/1/2014 21:00	2.30E+01	2.30E+01	0.00E+00	0.00E+00
10/1/2014 23:00	1.61E+01	1.59E+01	0.00E+00	0.00E+00
10/2/2014 1:00	1.04E+01	1.06E+01	0.00E+00	0.00E+00
10/2/2014 3:00	9.18E+00	9.34E+00	0.00E+00	0.00E+00

Re: how to create master calender link with bi-hourly time

its_anandrjs — Sat, 25 Oct 2014 18:36:09 GMT

Please explain about the bi-hourly time it is not understood.

Re: how to create master calender link with bi-hourly time

Sat, 25 Oct 2014 19:56:17 GMT

Sorry about that. If you see the data, the granularity is two hr. I want to create a calender only for existing hours. In this case 1, 3, 5, 7, ...., 23.

Re: how to create master calender link with bi-hourly time

jolivares — Sat, 25 Oct 2014 20:29:22 GMT

Separate your date field, that is, create a field date and another date_hour, the create a calendar with a date field to link with your table.

Is preferable that the hour is a field too in order you can filter over it.

Re: how to create master calender link with bi-hourly time

MarcoWedel — Sun, 26 Oct 2014 23:30:04 GMT

Hi,

one possible solution:

tabCalendar:
LOAD *,
    Hour(Date) as Hour,
    Day(Date) as Day,
    WeekDay(Date) as WeekDay,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Month(Date) as Month,
    MonthName(Date) as MonthName,
    Ceil(Month(Date)/3) as Quarter,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear;   
LOAD Date(AddYears(MinDate+(IterNo()-1)/12,-10),'MM/DD/YYYY hh:mm') as Date
While AddYears(MinDate+(IterNo()-1)/12,-10)<=Now();
LOAD Min(Date#(Date, 'MM/D/YYYY hh:mm')) as MinDate
FROM [http://community.qlik.com/thread/139502] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: how to create master calender link with bi-hourly time

Mon, 27 Oct 2014 18:13:43 GMT

i have divided date and time. created master calender and created a time field. But date and time does not synchronize. Here is my model and would you mind forward me a sample of master calender and time dimension. tx.

Re: Re: how to create master calender link with bi-hourly time

MarcoWedel — Tue, 28 Oct 2014 00:33:40 GMT

You asked here:Time as a Dimension

This is my thread how to create master calender link with bi-hourly time

you have provided a solution, but since i have to join 3 tables and each table has timestamp filed. I am able to create master calender but the issue time. Here is my list box from date and time. I would like to know why iam not getting unique time .

I suppose you created the Time listbox with an expression like

Time(Date)

You then will get multiple entries because this expression does not change the underlying numerical value.

One solution would then be:

Time(Frac(Date))

hope this helps also

regards

Marco

Re: Re: how to create master calender link with bi-hourly time

Tue, 28 Oct 2014 18:34:28 GMT

since i have multiple timestamp fields from six different tables, how do i join these fields?. Yes if i have one timestamp column then your suggestion would work. But i have 6 various timestamp fields and looking for a solution.

Re: Re: how to create master calender link with bi-hourly time

jolivares — Tue, 28 Oct 2014 18:46:03 GMT

Check this document...

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date