How to convert the time format “0.00:00:54” in to 54 secs (i.e. the final result should be 0.9 min)?

[sriabt]

In Qliksense script, how to convert the time format “0.00:00:54” in to 54 secs (i.e. the final result should be 0.9 min)? Because I need to use Avg, median later in the front end(Expression window), so need the final value as 0.9 like for above example. Please help to find the best solution. Thank you.

[BrunPierre]

This would be my approach

LOAD *,
num(Num#(TimeToSeconds) * 0.0166666667,'#.0') as SecondsToMinutes;
 
LOAD *,
Interval(Interval#(Time,'D.HH:MM:SS'),'SS') as TimeToSeconds;

DATA:
LOAD
'0.00:00:54' as Time
AutoGenerate(1);

[sriabt]

Thank you for your reply. Can you help to get the mins value for 0.00:02:53
values also?

Like 2.5 etc..

[BrunPierre]

Perhaps something like this 

LOAD * ,
NUM(NUM#(SubField(MinutesAndSeconds,':',1)&'.'&SubField(MinutesAndSeconds,':',2)),'#.#') as MinutesAndSecondsToDecimal;

LOAD * ,
Interval(Interval#(Time,'d.hh:mm:ss'),'mm:s') as MinutesAndSeconds;

DATA:
LOAD
'0.00:02:53' as Time
AutoGenerate(1);

[sriabt]

Hi Peter,

As per your new method, for this kind of values '0.00:02:53' its returning as 2.5 which is not correct and also for '0.00:00:54' its returning as 0.5(but 0.9 like before is the correct result).

Please check once and let me know the correct method to resolve this?

[MarcoWedel · Accepted solution]

LOAD *,
     Interval#(DateTime,'d.hh:mm:ss')*1440 as Minutes
Inline [
DateTime
0.00:02:53
0.00:00:54
];

hope this helps

Marco
 

[sriabt]

Thanks MarcoWedel. It working charm!!

Can you help me on this below issue?

Qliksense - VizlibCalendar : How to group the date values from raw data and show all those records

In Qliksense - we are using this VizlibCalendar filter purpose for Crd_Date field.

Now in the raw data(Crd_Date field) we have this format YYYY-MM-DD hh:mm:ss.

If we choose any date in the above calendar its showing wrong data like first date with different time values, etc..please let me know how to sort this out.

[sriabt]

In Qliksense - we are using this VizlibCalendar filter purpose for Crd_Date field.

Now in the raw data(Crd_Date field) we have this format YYYY-MM-DD hh:mm:ss.

If we choose any date in the above calendar its showing wrong data like first date with different time values, etc..please let me know how to sort this out.