topic Re: Need help !! in QlikView

Need help !!

nikhilgarg — Mon, 29 Dec 2014 05:56:25 GMT

HEllo ,

I am using personal edition of Qlikview and utilised 4-5 attempts of recovery. So i cannot open my own document.

I am attaching my application. Kindly send me the script that i have written in it so that i can again make it.

Thanks

Re: Need help !!

amit_saini — Mon, 29 Dec 2014 05:58:08 GMT

Nikhil,

There it is :

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

FROM

(ooxml, embedded labels, table is [Detaied Report]);

Left Join

Downtime:

LOAD

Trim(PurgeChar(Event,'')) as Event,

PurgeChar(StartDate,'.') as KeyStartDate,

PurgeChar(EndDate,'.') as KeyEndDate,

StartTime as Downtime_StartTime ,

EndTime as Downtime_EndTime,

'Planned' as Type

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,

Month_Year,

Duration,

KeyStartDate,

if(Type= 'Planned' ,'Planned','Unplanned') as Type

Resident Server_Analysis1;

drop Table Server_Analysis1;

Thanks,

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 06:03:03 GMT

Hey,

Thanks amit .

Re: Need help !!

amit_saini — Mon, 29 Dec 2014 06:03:56 GMT

No problem Nikhil

Thanks,
AS

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 06:12:46 GMT

Can you plz tell me script for this also ...

Thanks

Re: Need help !!

amit_saini — Mon, 29 Dec 2014 06:14:32 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,

PurgeChar(Time([Event Start Time] + '2:30:00', 'hh:mm:ss') , ':') as KeyStartTime,

Time([Event End Time] + '2:30:00', 'hh:mm:ss') as EndTime,

PurgeChar(Time([Event End Time] + '2:30:00', 'hh:mm:ss') , ':') as KeyEndTime,

MonthName([Event Start Time]) as Month_Year,

Interval(-(Date([Event Start Time]) - Date([Event End Time])))as Duration

FROM

(ooxml, embedded labels, table is [Detaied Report]);

Left Join

Downtime:

LOAD

Trim(PurgeChar(Event,'')) as Event,

PurgeChar(Date(StartDate , 'DD.MM.YYYY'),'.') as KeyStartDate,

PurgeChar(Date(EndDate , 'DD.MM.YYYY'),'.') as KeyEndDate,

Time((StartTime + '2:30:00'), 'hh:mm:ss') as Downtime_StartTime ,

PurgeChar(Time((StartTime + '2:30:00'), 'hh:mm:ss') , ':') as KeyDowntime_StartTime ,

Time((EndTime + '2:30:00'), 'hh:mm:ss') as Downtime_EndTime,

PurgeChar(Time((EndTime + '2:30:00'), 'hh:mm:ss'), ':') as KeyDowntime_EndTime,

Duration1 ,

Interval(-(Time([StartTime]) - Date([EndTime])))as Duration2,

'Planned' as Type ,

Reason

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Server_Analysis:

LOAD

Event,

KeyStartDate,

KeyEndDate,

StartDate,

EndDate,

StartTime,

KeyStartTime,

EndTime,

KeyEndTime,

Month_Year,

Duration,

Downtime_StartTime ,

KeyDowntime_StartTime ,

Downtime_EndTime,

KeyDowntime_EndTime,

Duration1,

// Duration2,

Reason,

if(Event = 'Server_DOWN' and KeyStartDate = KeyStartDate and KeyEndDate = KeyEndDate and KeyStartTime >= KeyDowntime_StartTime and KeyEndTime <= KeyDowntime_EndTime , 'Planned' , 'Unplanned' ) as Type

Resident Server_Analysis1;

DROP Table Server_Analysis1 ;

Downtime1:

LOAD Event as Event1,

StartTime as StartTime1,

EndTime as EndTime1,

StartDate as StartDate1,

EndDate as EndDate1,

Reason as Reason1,

Duration1 as Duration2

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 06:17:07 GMT

HEy,

Thanks . This was the script i was looking for .

Hope you don't mind if i ask for further help.

Thanks

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 07:09:00 GMT

HEy amit,

Can you please tell me in the 3rd pie chart(Expected_Planned_Downtime) that i havemade , what is the expression written??

Thanks

Re: Need help !!

amit_saini — Mon, 29 Dec 2014 07:12:38 GMT

Hi,

Server_DownTime_Analysis - YTD

=Sum({<Event = {'Server_DOWN'}>}Duration)

Server_Downtime_Analysis - Nov,2014

=Sum({<Event = {'Server_DOWN'} , Month_Year = {"Nov 2014"}>}Duration)

Assumed_Planned_Downtime

=Sum(Interval(-(Time([StartTime1]) - Date([EndTime1]))))

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 07:38:08 GMT

Thanks AMit,

I am done

Re: Need help !!

amit_saini — Mon, 29 Dec 2014 07:40:32 GMT

Good

Re: Need help !!

nikhilgarg — Mon, 29 Dec 2014 07:49:20 GMT

hey,

You on linkedin ?