HElp regarding script !!

[nikhilgarg]

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

[Anonymous]

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

On Qlik(Edit Screen) change LOAD statements.

Try it

[nikhilgarg]

Hey,

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

achieving wat i want ??

[almamy_diaby]

look at this

[Anonymous]

On CSV is more easy detect this kind of errors

[nikhilgarg]

HEy,

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

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

[its_anandrjs]

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

[almamy_diaby]

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;

[sujeetsingh]

Here it is

Join not working correctly !!

[nikhilgarg]

HEy ,

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