how to create master calender link with bi-hourly time

[#1]

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

[its_anandrjs]

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

[#3]

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.

[jolivares]

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.

[MarcoWedel]

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

[#6]

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.

[MarcoWedel]

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

[#8]

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.

[jolivares]

Check this document...

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