topic SV:Have an issue with my nested if in QlikView

Have an issue with my nested if

qw_johan — Sat, 30 Apr 2011 08:41:27 GMT

Hi,

I have an issue with my nested if...
I have a table with a LengthOfTime field. The format of the LengthOfTime data can vary.
Below are the different variations I have to be able to recognize and format correctly.
My script runs fine but there is one time format I can't seem to handle - '93,00', don't know why.
I include a little test qvw i made for this.

LengthOfTime
------------------------

214
93,00
1 min 30 s
02:34
00:03:15
1,15741E-05
0,000243056

I want to be able to disply all below formats like:
+ only seconds (for making calculations)
+ minutes and seconds (only for display to user)
+ hh:mm:ss (only for display to user)

Code to format the different variations into seconds:

/* As seconds */
if(IsNull(LengthOfTime),
'0',
if(IsText(Left(LengthOfTime,1)),
'0',
if(LengthOfTime = '' or LengthOfTime = ' ',
'0',
if(FindOneOf(LengthOfTime, 'min')>0,
((mid(LengthOfTime,1, Index(LengthOfTime, 'min')-1) * 60) + trim(TextBetween(LengthOfTime, 'min', 's'))),
if(FindOneOf(LengthOfTime, ':')>0,
if(SubStringCount(LengthOfTime, ':')=2,(Left(LengthOfTime, 2)*60*60) + (TextBetween(LengthOfTime, ':', ':')*60) + Right(LengthOfTime, 2),(Left(LengthOfTime, 2)*60) + Right(LengthOfTime, 2)),
if(FindOneOf(LengthOfTime, ',')>0,
(Left(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), 2)*60*60) + (TextBetween(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), ':', ':')*60) + Right(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), 2),
floor(LengthOfTime))))))) as tOnlySeconds

Thankful for any help with this,
Johan

SV:Have an issue with my nested if

ToniKautto — Sat, 30 Apr 2011 08:45:56 GMT

It looks like you are loading the value as a string, you probably need to convert it to a numeric value.

What are you expect 93,0 to represent time wise? 93 minutes?

SV:Have an issue with my nested if

qw_johan — Sat, 30 Apr 2011 09:34:55 GMT

93,00 = 93 seconds

SV:Have an issue with my nested if

qw_johan — Sat, 30 Apr 2011 11:42:21 GMT

I solved it !



 /* As seconds */
 if(IsNull(LengthOfTime),
 '0',

 if(IsText(Left(LengthOfTime,1)),
 '0',

 if(LengthOfTime = '' or LengthOfTime = ' ',
 '0',

 if(FindOneOf(LengthOfTime, 'min')>0,
 ((mid(LengthOfTime,1, Index(LengthOfTime, 'min')-1) * 60) + trim(TextBetween(LengthOfTime, 'min', 's'))),

 if(FindOneOf(LengthOfTime, ':')>0,
 if(SubStringCount(LengthOfTime, ':')=2,(Left(LengthOfTime, 2)*60*60) + (TextBetween(LengthOfTime, ':', ':')*60) + Right(LengthOfTime, 2),(Left(LengthOfTime, 2)*60) + Right(LengthOfTime, 2)),

 if(FindOneOf(LengthOfTime, ',')>0,
 if(Len(LengthOfTime)>6, (Left(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), 2)*60*60) + (TextBetween(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), ':', ':')*60) + Right(Time(Replace(LengthOfTime, ',', '.'), 'hh:mm:ss'), 2), num(Replace(LengthOfTime, ',', '.'))),

 floor(LengthOfTime))))))) as tOnlySeconds,