Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted

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

View solution in original post

9 Replies
Highlighted

Re: Negative time

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

You can replace the -120 with your field ABW.

Highlighted
Partner
Partner

Re: Negative time

i ll try it, thank you

Highlighted
Partner
Partner

Re: Negative time

this returns 00:02:00.

but i want to have - 00:02:00

Highlighted

Re: Negative time

Hi,

Try below

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

Replace -120 with your field

Regards

ASHFAQ

Highlighted
Master II
Master II

Re: Negative time

Hi.

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

Highlighted

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

View solution in original post

Highlighted
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

Highlighted
Not applicable

Re: Negative time

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

Highlighted
Creator
Creator

Re: Negative time

Appreciate this is an old thread but currently, QlikView supports -hh:mm format. You don't need to append a minus sign.