topic Re: HElp regarding script !! in QlikView

HElp regarding script !!

nikhilgarg — Thu, 04 Dec 2014 06:48:43 GMT

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

Re: HElp regarding script !!

Anonymous — Thu, 04 Dec 2014 11:49:21 GMT

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

On Qlik(Edit Screen) change LOAD statements.

Try it

Re: HElp regarding script !!

nikhilgarg — Thu, 04 Dec 2014 14:56:15 GMT

Hey,

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

achieving wat i want ??

Re: HElp regarding script !!

almamy_diaby — Thu, 04 Dec 2014 15:20:37 GMT

look at this

Re: HElp regarding script !!

Anonymous — Thu, 04 Dec 2014 15:24:10 GMT

On CSV is more easy detect this kind of errors

Re: HElp regarding script !!

nikhilgarg — Fri, 05 Dec 2014 05:20:15 GMT

HEy,

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

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

Re: HElp regarding script !!

its_anandrjs — Fri, 05 Dec 2014 11:43:51 GMT

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

Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 08:42:54 GMT

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;

Re: HElp regarding script !!

sujeetsingh — Mon, 08 Dec 2014 08:47:05 GMT

Here it is

Join not working correctly !!

Re: Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 09:29:33 GMT

HEy ,

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

Re: Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 09:38:26 GMT

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

Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 09:43:19 GMT

HEy,

But i have put some matching startdate , enddate and event that matches with server_analysis1.

Then , what is the problem ??

Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 09:52:51 GMT

send me the new file

Re: Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 09:57:10 GMT

PFA....

Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 11:48:20 GMT

HEy,

Can you help me out in this ... ??

Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 12:45:28 GMT

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;

Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 15:24:51 GMT

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 ??

Re: Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 16:09:20 GMT

make the times are the same

Re: HElp regarding script !!

nikhilgarg — Mon, 08 Dec 2014 16:36:15 GMT

Hey,

Sorry but i didnt get you....??

Re: Re: HElp regarding script !!

almamy_diaby — Mon, 08 Dec 2014 16:45:51 GMT

the value of StartTime is not the same as Downtime_StartTime

the value of EndTime is not the same as Downtime_EndTime