Server_Analysis1:
LOAD
PurgeChar(Event,'') as Event,
PurgeChar(Date([Event Start Time], 'DD.MM.YYYY'),'.') as KeyStartDate,
PurgeChar(Date([Event End Time], 'DD.MM.YYYY'),'.') as KeyEndDate,
Date([Event Start Time], 'DD.MM.YYYY') as StartDate,
Date([Event End Time], 'DD.MM.YYYY') as EndDate,
Time([Event Start Time] + '2:30:00', 'hh:mm:ss') as StartTime,
Time([Event End Time] + '2:30:00', 'hh:mm:ss') as EndTime,
MonthName([Event Start Time]) as Month_Year,
Interval(-(Date([Event Start Time]) - Date([Event End Time])))as Duration
// [Event Start Time] ,
// [Event End Time],
// [Event Duration],
//
// [EventState Information],
FROM
(ooxml, embedded labels, table is [Detaied Report]) ;
// STORE Server_Analysis1 into Server_Analysis1.qvd;
// DROP Table Server_Analysis1 ;
Left Join
Downtime:
LOAD
Trim(PurgeChar(Event,'')) as Event,
PurgeChar(StartDate,'.') as KeyStartDate,
PurgeChar(EndDate,'.') as KeyEndDate,
// Date(StartDate, 'DD.MM.YYYY') as StartDate,
// EndDate ,
// StartTime ,
// EndTime ,
StartTime as Downtime_StartTime ,
EndTime as Downtime_EndTime,
'Planned' as Type
// Lookup(Downtime_StartTime, Event , Event,Server_Analysis1) as dt
FROM
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Server_Analysis:
LOAD
Trim(Event) as Event,
Trim(StartDate) as StartDate,
Trim(EndDate) as EndDate,
StartTime,
EndTime,
Downtime_StartTime ,
Downtime_EndTime,
//
// [EventState Type],
// [EventState Information],
// MonName,
// Duration,
if(Type= 'Planned','Planned','Unplanned') as Type
Resident Server_Analysis1;
drop Table Server_Analysis1;
