3 Replies Latest reply: Apr 30, 2011 7:42 AM by Johan Adolfsson RSS

    Have an issue with my nested if

    Johan Adolfsson

      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
          Toni Kautto

          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
              Johan Adolfsson

              93,00 = 93 seconds

                • SV:Have an issue with my nested if
                  Johan Adolfsson

                  I solved it !Smile

                   



                  /* 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,