Returning times from INLINE table with minutes

[alespooletto]

I have currently created this INLINE table that contains, for each of the work centers, an associated standard time of completion. I want to make it so that Qlik reads these times as minutes and will therefore output a Time with, for ex: 00:24:08 after.

TempoMedioStandard:
LOAD 
    Cell, 
    Time#(TempoMedioStandardMin, 'hh.mm') as TempoMedioStandardMin_Time
INLINE [
    Cell, TempoMedioStandardMin
    21, 11.0
    22, 24.7
    23, 40.0
    24, 24.7
    25, 10.8
    26, 26.9
    27, 18.4
    29, 11.0
    30, 45.0
    31, 11.0
    32, 12.0
    33, 42.1
];

The issue is that while this seems to work, it only uses aggregated values instead of the specific times for each specific center. Like this: this is currently using MAX

[Or]

I'm a tad confused about multiple things here:

1) The format specified in time#() doesn't seem to match the format of the data - there's no hh.mm in sight?

2) If this is meant to be hh.m, then it's not a time#(), since some of them are larger than 24. Perhaps it's an Interval#()?

[alespooletto]

Hmm yeah, I'm not entirely sure if this function works as I wanted. 

Those values you see in the columns are supposed to be minutes. So a 42.1 value = 42 minutes and ~ 10 seconds. I tried with interval as follows:

TempoMedioStandard:
LOAD * INLINE [
    WorkCenter, TempoMedioStandardMin
    CELL21, Interval(0.008),
    CELL22, Interval(0.016979),
    CELL23, Interval(0.02809), 
    CELL24, Interval(0.016979),
    CELL25, Interval(0.00735),
    CELL26, Interval(0.018368), 
    CELL27, Interval(0.012905),
    CELL29, Interval(0.00794), 
    CELL30, Interval(0.03125), 
    CELL31, Interval(0.00794), 
    CELL32, Interval(0.008449), 
    CELL33, Interval(0.029282), 
];

But this doesn't seem to work and leaves my dashboard stuck on loading. It loads only as text. 

[hic]

So, if '42.1' means '00:42:10' then what does '26.9' mean? It can hardly mean 00:26:90', can it?

You could perhaps try

LOAD 

    Cell, 

    Interval(

Time#(Subfield(TempoMedioStandardMin,'.',1), 'm') + 

    Time#(Subfield(TempoMedioStandardMin,'.',2), 's') 

)  as TempoMedioStandardMin_Time

... 

[Or]

If this entire thing is in minutes to begin with,

Interval(TempoMedioStandardMin/1440,'hh:mm:ss') // Convert so that one day = 1

=Interval(42.9/1440,'hh:mm:ss')

[alespooletto]

@Or thank you for the answer, if I understand correctly, should I put that formula at the top to substitute the one I had before?

TempoMedioStandard:
LOAD 
    Cell, 
	Interval(TempoMedioStandardMin/1440,'hh:mm:ss') // Convert so that one day = 1
INLINE [
    Cell, TempoMedioStandardMin
    CELL21, 11.0
    CELL22, 24.7
    CELL23, 40.0
    CELL24, 24.7
    CELL25, 10.8
    CELL26, 26.9
    CELL27, 18.4
    CELL29, 11.0
    CELL30, 45.0
    CELL31, 11.0
    CELL32, 12.0
    CELL33, 42.1
];

[alespooletto]

Yes, those decimals are just indicative as percentage, not as actual time, so maybe .9 would be 90% to get to the next minute. But this specific calculation is not useful to me, I just need close and approximate numbers not super precise ones..

I will try a bit with your methods, thank you!

[Or]

You can use that in a Load statement, or on the front end in an expression, as you prefer.

[alespooletto]

Hey @Or  sorry if I bother you. I am trying but I'm not sure what I'm doing wrong here.. I use this formula just as you helped me write it:

TempoMedioStandard:
LOAD
Cell,
Interval(TempoMedioStandardMin/1440,'hh:mm:ss') as Time_Day
INLINE [
Cell, TempoMedioStandardMin
CELL21, 11.0
CELL22, 24.7
CELL23, 40.0
CELL24, 24.7
CELL25, 10.8
CELL26, 26.9
CELL27, 18.4
CELL29, 11.0
CELL30, 45.0
CELL31, 11.0
CELL32, 12.0
CELL33, 42.1
];

But this is the result:

[Or · Accepted solution]

Looks like you're running something else, not the inline - there's no WorkCenter field in the inline and the list of values doesn't match...

Perhaps the field being read isn't recognized as being numeric? In that case, you'll need to use num#() to tell Qlik how to read it as a number.

Additionally, max() returns a numeric dual, meaning if you want it formatted you'll need to use Interval(Max(Field)), I believe. Not sure about that, though.