Why is there a round at the end of a number converted from text?

Hello,

I am converting a text into a number to do a complex sort. It adds code from several different dimensions to create a large text. Then I convert it using value. When I do this, the final 3 digits are being rounded unnecessarily. Does anyone know how to get past this?

Original text: 23921101010004270452

Converted to number using VALUE: 2.3921101010004270000

In fact, when I paste it here, I am noticing that decimal in front. It's also something I don't want.

Best Answer

Answers

  • I tried and faced the same issue

    The line item starts with 2392, but the top right side section shows 2.392.

    Anaplan has a 15-character limit for decimal places. If you observe after the 15th character, it makes everything 0. So I guess this is an anaplan limit of decimal places. Since you mentioned you are using this for sorting, so I will suggest you to calculate the length of this code. For those which is less or equal to 15 character, give the sort. For those which are greater than 15 character, split the number (line item which is in Text format) into two parts.

    So taking this number as an example, 23921101010004270452 split it into two

    Part 1 239211010100042

    Part 2 70452

    For all such codes you get two parts of code. Then you rank part 1 and part 2 individually and combine them with a dot as separate and convert it into number.

    Part 1

    Rank Part 1

    Part 2

    Rank Part 2

    Final Rank

    239211010100042

    1

    70452

    1

    1.1

    239211010100042

    1

    70453

    2

    1.2

    239211010100044

    3

    70454

    1

    3.1

    239211010100045

    4

    70455

    1

    4.1

    239211010100046

    5

    70456

    1

    5.1

    Just an idea - See what's better with your requirement 😊

  • Great suggestions! Will try this out today and let you know. I noticed that it is taking a decimal too. I never intended a decimal to be there.

  • Update: I resolved this by using the text itself to Sort. Thanks to @KirillKuznetsov for that suggestion. I thought it had to be a number.

    I also put the larger sort item at the end. It's hard to explain this but the Job code that I am using will have at least 4 characters. By putting this at end instead of a 0/1 boolean, the rounding never happened. Not sure why this is but it works.

    Thanks all for the help!