Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Hi,
May be use function Alt(), like this
alt( Time#( Sum(Run) , 'Format 2013/14' ),
Time#( Sum(Run), 'Format 2015/16/17' ),
'No Time' )
please post some sample data... I would solve that in script... it's very hard to help you without sample data...
data posted
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.
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);
Thank you @Robin Hausdorfer - that worked fantastically for me