topic Re: Calculating time formate in expression in QlikView

Calculating time formate in expression

Anonymous — Mon, 22 Feb 2016 07:41:44 GMT

Hi Friends,

I have Available Hours in Hr Format and Down Time in HH:MM

Need to calculate

1. MTBF =(Availability Hours - Down time) / No of Faults (the result should be in Time period 'HH:MM:SS')

2. Ageing= as per Excel

Please find the excel attached also attached sample app with inline data.

Thanks in advance

Prakash

Re: Calculating time formate in expression

Chanty4u — Mon, 22 Feb 2016 08:31:19 GMT

create a maptable:

as per ur requirment ..u will get the fields in frnt end.. u cn directly use in chart

//AgingWeekSort:

//mapping load * Inline

//[ Defect_Aging_Weeks,Value

//1 Week,1

//2 Weeks, 2

//> 2 Weeks,3

//]

//;

//AgingDaysSort:

//Mapping LOAD * Inline

//[Defect_Aging_DayBands, Value1

//1 Day, 1

//2 Days, 2

//3 Days, 3

//> 3 Days, 4

//]

//;

Re: Calculating time formate in expression

daveamz — Mon, 22 Feb 2016 08:49:07 GMT

Hi Siva,

[Available Hrs] is integer and it needs to be parsed into interval type. Something like Interval#([Available Hrs], 'hh') as [Available Hrs Parsed].

Regards,

David

Re: Calculating time formate in expression

jagan — Mon, 22 Feb 2016 09:02:07 GMT

Hi,

Try like this

LOAD

Num#(Time#([Available Hrs], 'hh:mm:ss')) - Num#(Time#([Down Time]))/[No of Faults] AS AvailHrs;

LOAD Code,

[Down Time],

[No of Faults],

[Available Hrs]

FROM

[MTBF.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now create a Straight table

Dimension:

Code,

[Down Time],

[No of Faults],

[Available Hrs] ,AvailHrs

Expession: Aging

=If(AvailHrs >= 11, '>11 Days',

If(AvailHrs >= 6, '6 to 10 Days',

If(AvailHrs >= 1, '1 to 5 Days', '<24 Hours')))

Re: Calculating time formate in expression

jagan — Mon, 22 Feb 2016 09:07:15 GMT

PFA file for solution.

Regards,

jagan.

Re: Calculating time formate in expression

Anonymous — Mon, 22 Feb 2016 09:19:19 GMT

Hi all,

Thanks for reply, don't load the excel and please work on which i'm attached file qvw, because i didn't get available hrs data in time stamp i'm getting in hours like 744. please reply ASAP

Re: Calculating time formate in expression

Anonymous — Mon, 22 Feb 2016 09:34:59 GMT

available time - down time

Re: Calculating time formate in expression

settu_periasamy — Mon, 22 Feb 2016 10:01:17 GMT

Hi,

may be like this..

Edit: removed Ceil

Directory;
T1:
LOAD *,Interval(([Available Hrs]-[Down Time])/[No of Faults],'hh:mm') as MTFB;
LOAD Code,
[Down Time],
[No of Faults],
[Available Hrs]
FROM
[..\MTBF.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
T2:
LOAD *,if(MTFB>10, '11 to 20 Days',
                if(MTFB>5, '6 to 10 Days',
                     if(MTFB>=1, '1 to 5 Days',
                     '<=24 hours'))) as Ageing Resident T1;
Drop Table T1;

Re: Calculating time formate in expression

settu_periasamy — Mon, 22 Feb 2016 10:26:37 GMT

Hi,

Try this..

T1:
LOAD *,if(MTFB>10, '11 to 20 Days',
  if(MTFB>5, '6 to 10 Days',
  if(MTFB>=1, '1 to 5 Days',
  '<=24 hours'))) as Ageing;
LOAD *,Interval((Interval#([Available Hrs],'hh')-Interval#([Down Time],'hh:mm'))/[No of Faults],'hh:mm') as MTFB;
LOAD * INLINE [
    Code,Down Time,No of Faults,Available Hrs
    1, 64:29, 23, 744
    2, 92:02, 22, 744
    3, 51:58, 19, 744
    4, 28:09, 19, 744
    5, 191:27, 18, 744
    6, 150:24, 17, 744
    7, 119:24, 16, 744
    8, 542:49, 16, 744
    9, 21:25, 6, 744
    10, 8:33, 6, 744
    11, 3:51, 3, 744
    12, 3:49, 3, 744
    13, 21:43, 3, 744
];

Re: Calculating time formate in expression

jagan — Tue, 23 Feb 2016 00:42:55 GMT

HI,

Please try below script

LOAD *,

If(MTFB > 10, '> 11 Days',

If(MTFB > 5, '6 - 10 Days',

If(MTFB >= 1, '1 - 5 Days', '<24 Hrs'))) AS AgeBucket;

LOAD

Code,

[Down Time],

[No of Faults],

[Available Hrs],

Interval#(AvailHrs - DownHrs)/[No of Faults] AS MTFB;

LOAD Code,

[Down Time],

[No of Faults],

[Available Hrs],

Interval#([Available Hrs], 'hh') AS AvailHrs,

Interval#([Down Time], 'hh:mm') AS DownHrs;

LOAD * INLINE [

Code,Down Time,No of Faults,Available Hrs

1, 64:29, 23, 744

2, 92:02, 22, 744

3, 51:58, 19, 744

4, 28:09, 19, 744

5, 191:27, 18, 744

6, 150:24, 17, 744

7, 119:24, 16, 744

8, 542:49, 16, 744

9, 21:25, 6, 744

10, 8:33, 6, 744

11, 3:51, 3, 744

12, 3:49, 3, 744

13, 21:43, 3, 744

];