times in different formats causing problems

[timsaddler]

Hi All

How do i force a mixture of times to be the same?

Here are times from 5 years of triathlon data.

Years 2013/2014 are in a different format.

I can't see a way of doing this elegantly.

Can someone give me a few ideas how to ?

Screenshot shows an example

Thanks in advance

Message was edited by: Tim Saddler data as requested

[ahaahaaha]

Hi,

May be use function Alt(), like this

alt( Time#( Sum(Run) , 'Format 2013/14' ),

Time#( Sum(Run), 'Format 2015/16/17' ),

'No Time' )

[Anonymous]

please post some sample data... I would solve that in script... it's very hard to help you without sample data...

[timsaddler]

data posted

[tcullinane]

Your data is wrong/needs cleansing. 2013/2014 are correct giving hh:mm:ss format but the other years are a bit of a jumble, if you reformat all times to display in the excel 'time' format you should get a better understanding, minutes are entered as hours and seconds are entered as minutes in most cases. (the total column looks correct through the years as do a few of the columns in 2014/2015)

If this is all your data, you are better off correcting it in excel (divide by 60 the offending data) and then import the corrected data.

[Anonymous · Accepted solution]

the following solution is for QlikView, but should work for QlikSense as well by linking a lib...

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

LOAD Year,
Name,
Swim,
Interval(Interval#(if(left(text(Interval(Swim)),1)>0,'00:'&

left(text(Interval(Swim)),5),text(Interval(Swim))),'hh:mm:ss'))  as Swim_ok,    
T1,
Interval(Interval#(if(left(text(Interval(T1)),2)>0,'00:'&

left(text(Interval(T1)),5),text(Interval(T1))),'hh:mm:ss'))  as T1_ok,    
Bike,
Interval(Interval#(if(left(text(Interval(Bike)),1)>0,'00:'&

left(text(Interval(Bike)),5),text(Interval(Bike))),'hh:mm:ss'))  as Bike_ok,
T2,
Interval(Interval#(if(left(text(Interval(T2)),2)>0,'00:'&

left(text(Interval(T2)),5),text(Interval(T2))),'hh:mm:ss'))  as T2_ok,    
Run,
Interval(Interval#(if(left(text(Interval(Run)),1)>0,'00:'&

left(text(Interval(Run)),5),text(Interval(Run))),'hh:mm:ss'))  as Run_ok
FROM [TRI_Data2.xlsx] (ooxml, embedded labels, table is All);

[timsaddler]

Thank you @Robin Hausdorfer - that worked fantastically for me