Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Because, for my application I need to filter the data:
How can I achieve this?
I hope to find a suitable solution. When something is not clear, I will try to clarify it.
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
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
Thank you!
That did the trick!