Field association with datetime and master calendar.

Mon, 25 Feb 2013 10:07:56 GMT

Dear Qlikview users,

I have an excelsheet with temperatur and energy data. All energy and temperature values are registred every hour of a day. I want to make use of a Master Calendar to be able to filter the data on specific days and hours. However, when I import the data of the sheet, the associations between this data and the fields of the master calendar are not exactly how I want it.

Before I go into the details, I want to give you an impression of the data and script.
I've added it below.

Excelsheet with data (click to expand):

Script for importing excelsheet:
Cofely:
LOAD timestamp#(Date(A) & ' ' & Time(A),'DD/MM/YYYY hh:mm:ss') as Cofely.DatumTijd, 
     B as Cofely.Dag, // Day
     C as Cofely.Uur, // Hour
     D as Cofely.BuitenTemperatuur, // Temperature
     G as Cofely.Elektrisch, // Energy
     H as Cofely.Gas, // Gas
     Date(A) as Cofely.Datum, // Date
     Time(A) as Cofely.Tijd  // Time
FROM
(ooxml, explicit labels, header is 3 lines, table is Blad1);

Mastercalendar snippet:

QuartersMap:
MAPPING LOAD 
rowno() as Month,
'Kw' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
   Date(date#('01-01-2010','DD-MM-YYYY')) as minDate,
   Today() as maxDate
Resident Cofely;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
          $(varMinDate) + Iterno()-1 As Num,
          Date($(varMinDate) + IterNo() - 1) as TempDate
    AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
   TempDate AS Cofely.Datum,
   week(TempDate) As Calendar.Week,
   Year(TempDate) As Calendar.Jaar,
   Month(TempDate) As Calendar.Maand,
   Day(TempDate) As Calendar.Dag,
   YeartoDate(TempDate)*-1 as Calendar.HuidigYTDFlag,
   YeartoDate(TempDate,-1)*-1 as Calendar.LaatsteYTDFlag,
   inyear(TempDate, Monthstart($(varMaxDate)),-1) as Calendar.RC12,
   date(monthstart(TempDate), 'MMM-YYYY') as Calendar.MaandJaar,
   ApplyMap('QuartersMap', month(TempDate), Null()) as Calendar.Kwartaal,
   Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as Calendar.WeekJaar,
   if(weekday(TempDate) < 5, 'Werkdagen', 'Weekend') as Calendar.PeriodeType,
   WeekDay(TempDate) as Calendar.WeekDag
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

As you can see in the excelsheet, on every row there is a datetime column which represents the date and every hour of that day.
The master calendar is linked with the "Cofely.Datum"-column (which is only the date). The result of this is that there are multiple date-values.
For example, the value "01-01-2010" occurs multiple times, because this datevalue exists for every hour.
So when i select the first "01-01-2010" it is linked to "1:00" hour (24h format) . The second "01-01-2010" is linked to "2:00" hour etc. etc.

I've added an example below.

Association example;

I want to achieve the following:

When I select a date, all hours must be selected in it (1:00 - 24:00)

Because, for my application I need to filter the data:

Per chosen day period
Per chosen time period

How can I achieve this?

I hope to find a suitable solution. When something is not clear, I will try to clarify it.

Re: Field association with datetime and master calendar.

Gysbert_Wassenaar — Mon, 25 Feb 2013 14:46:42 GMT

Date(A) as Cofely.Datum, does not remove the time fraction of the datetime. The date function only changes the formatting. You'll want to use the floor function to create a date without time fraction. And frac for time.

Date(floor(A)) as Cofely.Datum

Time(frac(A)) as Cofely.Tijd

Re: Field association with datetime and master calendar.

Mon, 25 Feb 2013 20:12:53 GMT

Thank you!
That did the trick!

topic Re: Field association with datetime and master calendar. in QlikView

Field association with datetime and master calendar.

Re: Field association with datetime and master calendar.

Re: Field association with datetime and master calendar.