Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
anyone knows how to sum two fields with the format of hh:mm:ss tt?
Pls find attached.
I would probably do it like this:
Interval(Sum(TimeField1 + TimeField2), 'D hh:mm:ss')
=Rangesum( Interval#(FIELD1, 'hh:mm:ss tt'),Interval#(FIELD2, 'hh:mm:ss tt') )
=Interval(Timestamp#(Field1,'hh:mm:ss tt') + Timestamp#(Field2,'hh:mm:ss tt'),'hh:mm:ss')
thank you all.. none of the above worked for me and here is why but still dont know the reason
the first field format is mm/dd/yyyy hh:mm:ss tt
the second field format is hh:mm:tt
I should've added that on my question..
Let me know how I can fix it and thank you for all your help!
Alec
So are you expecting a datefield result or just the sum of time for the two fields? I guess it would be helpful if you can share a couple of lines of input with their expected output here
Sunny,
thank you for always helping us here..
to give you more thoughts the first field has a date and time for example: 1/25/2016 9:30:20 am
the second field is task average run time such as 1:30:20
the final field I need should return the sum of the start date and the average run time= 11:00:40 am
Hope this explains it..
You should start with interpreting the field values using interpretation functions and appropriate format codes:
LOAD FIELD1,
Timestamp#(FIELD1, 'MM/DD/YYYY hh:mm:ss tt') as FIELD1_,
FIELD2,
Interval#(FIELD2, 'hh:mm:tt') as FIELD2_
FROM YourTable;
Take care that the format codes are really matching the input values!
Check that all values in fields FIELD1_ and FIELD2_ now have a numeric representation.
Then you can sum the field values as you like:
Sum(FIELD1_) , Sum(FIELD2_), Sum(RANGESUM(FIELD1_, FIELD2_))
Finally, you can format the values using format functions: Timestamp(), Interval(), etc.
May be something like this:
=Time(Time('1/25/2016 9:30:20 AM') + Time#('1:30:20', 'h:mm:ss'), 'hh:mm:ss TT')
Make sure TimeStamp and Time are read as TimeStamp/Time fields cause if they are not, then you will have to use TimeStamp#() and Time#() to help QlikView understand that they are TimeStamp and Time fields, respectively.
HTH
Best,
Sunny
tried to build the two fileds but still getting the same format.. (Image attached)