Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
In my application,
I have 2 different excel sheets.
In one sheet all that time is mentioned at which server is down
In second sheet a planned time is mentioned.
Now i want a column to be shown which shows planned or unplanned downtime matching timimg in both sheets.
But unplanned downtime is shown everywhere.
What to do ??
Thanks
the joint works well. the problem is that no unplanned downtime data file does not match the file containing all the downtime .
any StartTime, StartDate do not match. although analyzes the two files
HEy,
But i have put some matching startdate , enddate and event that matches with server_analysis1.
Then , what is the problem ??
send me the new file
PFA....
HEy,
Can you help me out in this ... ??
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;
Hey,
Will it give me the same result that i want i mean for each matching event,
startdate and enddate , will it append the downtime start and end time ??
make the times are the same
Hey,
Sorry but i didnt get you....??
the value of StartTime is not the same as Downtime_StartTime
the value of EndTime is not the same as Downtime_EndTime