topic Re: times in different formats causing problems in Visualization and Usability

times in different formats causing problems

timsaddler — Mon, 03 Jul 2017 13:29:12 GMT

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

Re: times in different formats causing problems

ahaahaaha — Mon, 03 Jul 2017 13:41:05 GMT

Hi,

May be use function Alt(), like this

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

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

'No Time' )

Re: times in different formats causing problems

Anonymous — Mon, 03 Jul 2017 13:41:27 GMT

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

Re: times in different formats causing problems

timsaddler — Mon, 03 Jul 2017 14:01:40 GMT

data posted

Re: times in different formats causing problems

tcullinane — Mon, 03 Jul 2017 15:30:34 GMT

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.

Re: times in different formats causing problems

Anonymous — Mon, 03 Jul 2017 15:35:31 GMT

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

Re: times in different formats causing problems

timsaddler — Tue, 04 Jul 2017 12:41:14 GMT

Thank you @Robin Hausdorfer - that worked fantastically for me