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
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;
For each file: Open Excel and Save as as CSV format.
On Qlik(Edit Screen) change LOAD statements.
Try it
Hey,
Sorry but i didn't get you. Can you plz explain ?? Will it help me
achieving wat i want ??
look at this
On CSV is more easy detect this kind of errors
HEy,
Actually i am using personal edition so cannot open your qvw.
So can you kindly tell me here what to do ??
Hi NIkhil,
In your second table try to load like this way and format the date field according to the date format because some time when reading into qlikview format not match with excel.
DownTime:
LOAD [EventState Type] as Event ,
StartTime ,
EndTime,
DATE( date#(StartDate,'DD.MM.YYYY'),'DD.MM.YYYY') as StartDate,
DATE( date#(EndDate,'DD.MM.YYYY'),'DD.MM.YYYY') as EndDate
From
Planned_Downtime_Sheet.xlsx
(ooxml, embedded labels, table is Sheet1);
Regards
Anand
Server_Analysis1:
LOAD
PurgeChar([EventState Type],' ') as EventStateType,
Date([Event Start Time], 'DD.MM.YYYY') as StartDate,
Date([Event End Time], 'DD.MM.YYYY') as EndDate,
Time([Event Start Time] , 'hh:mm') as StartTime,
Time([Event End Time] , 'hh:mm') as EndTime,
// Time([Event Start Time] + '2:30:00', 'hh:mm') as StartTime,
// Time([Event End Time] + '2:30:00', 'hh:mm') as EndTime,
[Event End Time],
[Event Duration],
[EventState Type],
[EventState Information],
[Event Start Time],
MonthName([Event Start Time]) as MonName,
//Interval( Date('2010-11-19 09:22:09.000') - Date( '2010-11-19 11:52:38.000'), 'hh:mm:ss') as Diff
Interval(-(Date([Event Start Time]) - Date([Event End Time])))as Duration_Time
FROM
(ooxml, embedded labels, table is [Detaied Report]);
Left Join
DownTime:
LOAD PurgeChar([EventState Type], ' ')as EventStateType,
StartDate,
EndDate,
StartTime,
EndTime,
'Planned' as Type
FROM
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Server_Analysis:
LOAD
EventStateType,
StartDate,
EndDate,
StartTime,
EndTime,
[EventState Type],
[EventState Information],
MonName,
Duration_Time,
if(Type= 'Planned','Planned','Unplanned') as Type
Resident Server_Analysis1;
drop Table Server_Analysis1;
Here it is
HEy ,
It is also not working . Your above script insert a new row for planned