topic Re: Returning times from INLINE table with minutes in App Development

Returning times from INLINE table with minutes

alespooletto — Mon, 15 Jan 2024 10:30:23 GMT

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

Re: Returning times from INLINE table with minutes

Or — Mon, 15 Jan 2024 12:24:48 GMT

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#()?

Re: Returning times from INLINE table with minutes

alespooletto — Mon, 15 Jan 2024 12:36:02 GMT

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.

Re: Returning times from INLINE table with minutes

hic — Mon, 15 Jan 2024 12:48:43 GMT

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

...

Re: Returning times from INLINE table with minutes

Or — Mon, 15 Jan 2024 13:14:56 GMT

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')

Re: Returning times from INLINE table with minutes

alespooletto — Mon, 15 Jan 2024 14:18:08 GMT

@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 ];

Re: Returning times from INLINE table with minutes

alespooletto — Mon, 15 Jan 2024 14:19:57 GMT

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!

Re: Returning times from INLINE table with minutes

Or — Mon, 15 Jan 2024 14:21:03 GMT

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

Re: Returning times from INLINE table with minutes

alespooletto — Tue, 16 Jan 2024 12:24:00 GMT

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:

Re: Returning times from INLINE table with minutes

Or — Tue, 16 Jan 2024 12:34:18 GMT

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.

Re: Returning times from INLINE table with minutes

alespooletto — Tue, 16 Jan 2024 14:02:24 GMT

Thank you @Or . I found the reason for this not working, the decimal separator!!

In my main log, I use ',' and not '.' , now everything works correctly... After many trials and errors, I ended up using this:

Interval(Num#(TempoMedioStandardMin/1440),'hh:mm:ss') as Time_Day