Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

HElp regarding script !!

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

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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;

View solution in original post

20 Replies
Highlighted
Creator II
Creator II

For each file: Open Excel and Save as as CSV format.

On Qlik(Edit Screen) change LOAD statements.

Try it

Highlighted
Specialist
Specialist

Hey,

Sorry but i didn't get you. Can you plz explain ?? Will it help me

achieving wat i want ??

Highlighted
Creator
Creator

look at this

Highlighted
Creator II
Creator II

On CSV is more easy detect this kind of errors

Highlighted
Specialist
Specialist

HEy,

Actually i am using personal edition so cannot open your qvw.

So can you kindly tell me here what to do ??

Highlighted

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

Highlighted
Creator
Creator

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;

Highlighted
Master III
Master III

Highlighted
Specialist
Specialist

HEy ,

It is also not working . Your above script insert a new row for planned