Error value in field after derive transformation

Kobi Averbuch 26 Reputation points Microsoft Employee

I am trying to get the unsigned value of a signed integer in a derive transformation for column with numeric values with the following

toInteger(iif(toInteger($$)<0, power(2, 32),toDouble(0))+toDouble($$))

I am getting an internal error - all negative values get the value '2147483647'

What can be the issue here?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,159 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. MarkKromer-MSFT 5,186 Reputation points Microsoft Employee

    Are you looking for absolute value? abs() ?

    0 comments No comments

  2. Kobi Averbuch 26 Reputation points Microsoft Employee

    no. for example, i am trying the get the unsigned value of -20, which is not 20 (abs) but -20+2^32
    i think i get some overflow internally at that procedure but it is not clear why.

    the 2147483647 i get is kind of generic error value

    0 comments No comments

  3. svijay-MSFT 5,201 Reputation points Microsoft Employee

    Hello @Kobi Averbuch ,

    Thanks for the question and using MS Q&A platform.

    I checked your expression - I checking the output at the level

    iif(toInteger($$)<0, power(2, 32),toDouble(0))+toDouble($$)  

    I was getting the number in the in the scientific format. number-E-x


    From research, currently toInteger cannot handle scientific format of numbers.

    I did modify the expression and i got a simlar output at my end.

    So I came up with the below expression that gives the required output

    toDecimal(iif(toInteger(-20)<0, toDecimal(power(2, 32),12),toDecimal(0))+toDecimal(-20),12,0)  

    I have used -20 for illustration but you can replace it at your end.


    -20+2^32= 4294967276

    Note :

    Integer Range is between : -2147483648 to 2147483647.
    Unsigned integer range would be between 0 to 4294967295

    You may not be able to use the toInteger(<iif.....>)

    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators