Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

lukaspuschner
Not applicable

Negative time

Hello,

I have a filed with positive and negative secconds.

i want to have it in a time format. thats no problem for the positive values, but hot to make -120 secconds to - 00:02:00 ?

time(Floor((ABW) / 3600) & ':' &  Floor(mod((ABW), 3600)/60) & ':' & mod((ABW) , 60),'hh:mm:ss')

this is the code i have

Tags (2)
1 Solution

Accepted Solutions
ashfaq_haseeb
Not applicable

Re: Negative time

Hi,

Try below in load script.

Load *,if((Time)<0,'-'&interval(timestamp#(fabs(Time), 's'), 'hh:mm:ss'),interval(timestamp#(fabs(Time), 's'), 'hh:mm:ss')) as Test;

Load * Inline

[

Time

-120

120

];

Regards

ASHFAQ

8 Replies
bill_markham
Not applicable

Re: Negative time

= time ( fabs ( (-120) ) / 24 / 60 / 60   ,'hh:mm:ss')

You can replace the -120 with your field ABW.

lukaspuschner
Not applicable

Re: Negative time

i ll try it, thank you

lukaspuschner
Not applicable

Re: Negative time

this returns 00:02:00.

but i want to have - 00:02:00

ashfaq_haseeb
Not applicable

Re: Negative time

Hi,

Try below

=interval(timestamp#(fabs(-120), 's'), 'hh:mm:ss')

Replace -120 with your field

Regards

ASHFAQ

whiteline
Not applicable

Re: Negative time

Hi.

Use Interval function instead of time. Time can't be negative.

ashfaq_haseeb
Not applicable

Re: Negative time

Hi,

Try below in load script.

Load *,if((Time)<0,'-'&interval(timestamp#(fabs(Time), 's'), 'hh:mm:ss'),interval(timestamp#(fabs(Time), 's'), 'hh:mm:ss')) as Test;

Load * Inline

[

Time

-120

120

];

Regards

ASHFAQ

Not applicable

Re: Negative time

Hei ~

I don't know if you need the exact Time object or just the representation,

how about something like this for the representation

=If (TimeField< 0, '-', '') & Maketime(Floor(TimeField/3600), Floor(mod((TimeField), 3600)/60), Mod((120) , 60))

that would yield -12:02:00 am...

just add the desired format to the MakeTime function using Time, or Interval like this (this one gives exactly the one from your example, except the negative Time object)

=If(-120 < 0, '-', '') & Interval(Maketime(Floor(fabs(-120)/3600), Floor(mod((fabs(-120)), 3600)/60), Mod((fabs(-120)) , 60)), 'hh:mm:ss')

where -120 is your original example (ABW)

I don't really know if QlikView supports "Negative time values" per se, but this is a workaround you visualization anyways,

hope it helps, c ya

Not applicable

Re: Negative time

Load *,if((Time)<0,'-', '') & Interval(timestamp#(fabs(Time), 's'), 'hh:mm:ss') as Test; // code compression FTW, ;D