Converting exponential notation to integer

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 5:51 am

I assume that this cannot be done at source and/or target?

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 5:56 am

Failing that - doesn't the E represent the number of places to move the decimal?

So using the E as a delimiter split your values into the digit (the first part) and the exponential and just multiply them out?
6.4443433E7 = 6.4443433*10^7

ArndW · Post by **ArndW** » Mon Jul 13, 2015 6:22 am

The data cannot be correctly read in at the source (thanks to an excel source file with 600K rows and the way that the unstructured data stage works).
The "E" is indeed base 10 exponential; I thought about the mathematical approach and think that it will take longer than the string approach.

Code: Select all

svTrimNoLeading[1,LEN(svTrimNoLeading)-2]*(10**Right(svTrimNoLeading,1)

I should add that the output value is a string column, so the computation above still needs to be converted from a decimal/numeric to a string using some method such as the embedded trims above.

Addendum - I just ran a test and the original solution is faster.

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 6:36 am

So you cannot just convert to dfloat to decimal, then decimal to string and suppress zeros?

ArndW · Post by **ArndW** » Mon Jul 13, 2015 7:01 am

The decimal-to-string conversion just has the "fix_zero" option to allow a 0 value, but no suppress zeroes.

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 7:11 am

When did this happen. Did they remove the "suppress_zero" option?

ArndW · Post by **ArndW** » Mon Jul 13, 2015 7:48 am

I can't find it in my documentation for the DecimalToString.

priyadarshikunal · Post by **priyadarshikunal** » Mon Jul 13, 2015 7:53 am

Did you try AsInteger()? I think it works with dfloat too. Truncates the mantissa (decimal part) though.

ArndW · Post by **ArndW** » Mon Jul 13, 2015 8:19 am

Yes, I did try AsInteger(). It does work with dfloat, but converts to a binary representation and thus isn't feasible in this context.

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 8:25 am

ArndW wrote:I can't find it in my documentation for the DecimalToString.

I am pretty sure its still there. It would be "suppress_zero"

ArndW · Post by **ArndW** » Mon Jul 13, 2015 9:49 am

Cool - the statement is now

Code: Select all

DecimalToString(DFloatToDecimal(svtest),"suppress_zero")

and that is faster and easier to understand.

ShaneMuir · Post by **ShaneMuir** » Mon Jul 13, 2015 9:55 am

ArndW wrote:that is faster and easier to understand. ...

Excellent! And good to know that its faster.