2 Replies Latest reply on Dec 22, 2014 10:03 PM by malou.puyod

    Wrong values when I convert string to integer

    malou.puyod

      I need to get the MBPS value from a field which is string.  The number value that I will extract will be used to get the % Utilization so I'm converting the string to integer.  I was able to convert it to integer BUT majority of the values I get are incorrect - sometimes double and a few is triple the value. 

       

      I need to get the number value from a string preceding the word MBPS or MB.

       

      Sample:  IC-AAZ R. AVENUE 4MBPS  ----> value should be 4

                    IC-BBB 5MB  ---> should be 5

       

      Here is my calculated field in order to get the number:

      Circuit Usage =

      IF CONTAINS([CIRCUIT ID], 'Mbps') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'Mbps') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'MBPS') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'MBPS') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'mbps') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'mbps') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'MB') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'MB') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'mb') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'mb') -2,2))

      ELSE ''

      END

       

      The above formula is correct because it displays the correct value however in string form.  So here's my 2nd formula which is displaying wrong values:

      INT([Circuit Usage])

       

      I also tried FLOAT([Circuit Usage]) but I also have the same problem.

       

        

      CORRECTCircuit Capacity
      48
      816
      1020
      1831
      55
      612
      612
      1016
      44
      55
      816
      3070

       

      Attached is my sample packaged workbook.  I hope someone can help me on this.

       

       

      Regards,

      Lou

        • 1. Re: Wrong values when I convert string to integer
          Jeff D

          Hi Lou,

           

          The reason you are seeing double or triple is because you're using SUM and you have two or three rows for the same Circuit ID.  For example, for "BDO CM RECTO 10Mbps", if you view the data, then click on "Underlying", you'll see there are two rows, so the sum is 20 rather than 10.  If you have three rows, you'd get triple the value.

           

          One way to fix this is to use MIN instead of SUM.  Since all rows for the same Circuit ID should have the same value, you can use MIN or MAX and the result would be the same.

          • 2. Re: Wrong values when I convert string to integer
            malou.puyod

            Thanks Jeff!    I will apply your solution.  I'm not sure if using Min or Max will also result to correct values if there are multiple circuits with XX Mbps values per customer in the future.