Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Field association with datetime and master calendar.

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):

qv_data_ex.PNG


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;

qv_ex.PNG

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Field association with datetime and master calendar.

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


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Field association with datetime and master calendar.

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


talk is cheap, supply exceeds demand
Not applicable

Re: Field association with datetime and master calendar.

Thank you!
That did the trick!