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 [C:\qlikview\dashboards\dataset.xlsx] (ooxml, explicit labels, header is 3 lines, table is Blad1);
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.
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.