We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Handling large floating numbers

Comments

12 comments

  • Avatar
    Ron Li

    Hi Thomas,

     

    I encountered the same problem and it can be easily solved by using the "Modify Fields" Node.

    You may indicate the Format for different fields in "Modify Fields" Node, following the convention of Java Formatter.

    e.g. 1.23456789E9 --> 123456789     using Format : %.0f

    .0 = mean no decimal place.

    For more formats, please refer to :https://docs.oracle.com/javase/7/docs/api/java/util/Formatter.html

    Attached is the example for your reference.

    Hope it helps.

     

    Attached files

    FormatterExample.lna

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Thank you Ron for your solution. The Modify Fields node's Help documentation ('Node help' > 'Aggregation and Transformation' > 'Modify Fields') discusses the available conversion options when using the node - see the 'Using a Format to modify an output field' section. The Help is also available online for the latest version of the product here: 

    https://d3sa-preview.infogixsaas.com/docs/dist/help/Content/e-node-help/Aggregation_and_Transformation/modify-fields.html

     

    If your floating point numbers are always whole numbers you can also cast them to a long data type in the Modify Fields node. Note however, the number would effectively be rounded towards zero if the number was not a whole number.

     

    One very minor point: Analyze leverages Java 8 so, as noted in the Help, the formatter documentation is here:

    https://docs.oracle.com/javase/8/docs/api/java/util/Formatter.html 

    0
    Comment actions Permalink
  • Avatar
    Thomas Wong

    Hi Ron and Adrian,

    Thanks for the help.

    I think we can use the modify field node formatting to set to the required display - however as string, we may not use them for further arithmetic purpose within the data flow.

    I would actually use this in the final output node or the end of a long series of computation sequence. Output to excel will have the cells as text/string values. It may not be desired for the case where we output to a template which a particular cell may be required as a floating number (without the scientific notation).

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    If you are dealing with calculations on financial transaction data you may also be interested the discussion in this post relating to intrinsic inaccuracies that can occur in floating point arithmetic.

    https://support.infogix.com/hc/en-us/community/posts/360035102494-Floating-point-division-returning-incorrect-values

     

    Note: our Data3Sixty DQ+ product supports decimal and currency data types in addition to floating point, integer and big integer data types.

    0
    Comment actions Permalink
  • Avatar
    Geoff

    Using lavastorm, I often used round() to fix this, I was just noticing in converting Lava to DS360 it doesnt work the same?

    I am rounding a value in a transform step:

    out1.GL_LINE_AMOUNT=round(in1.GL_LINE_AMOUNT,2)

     

    and then summing it in an Agg...

    outputs[0]["Total"] = group.sum(fields["GL_LINE_AMOUNT"])

     

    in Lavastorm, the output in the agg is 11836239.68

    in DS360 its 1.183623968000001E7

    if its already rounded in the previous step... how is it going back to scientific?

     

    EDIT:

    scratch that...

    using a 2nd transform to then round the result still doesnt show it as a normal double?? I need to format these so I can see the real numbers. NOT scientific...

    Total 1.183623968000001E7

    Total2 (Rounded) 1.183623968E7

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    The Data Viewer component within Data360 Analyze automatically formats large floating point numbers to scientific notation for display purposes. The underlying number is stored as a floating point number. There is no user preference control to configure the format of the displayed number within the Data Viewer.

    Not all Real numbers can be represented exactly using the fixed number of bits employed by floating point numbers. The process of rounding a floating point number may itself result in an approximate number representing the rounded value.

    Casting the number to a string will display the digits. Similarly, using the Output Delimited node will write the numeric value to the file

    0
    Comment actions Permalink
  • Avatar
    Geoff

    This is disappointing... especially since there was never an issue like this in Lavastorm. Trying to convert the brg to Ina will be that much harder since I will need to convert the number over and over to compare and make sure it matches.

    FYI, importing the brg isnt even close to working... i have tons of errors so I have to go through each node one at a time to fix it, comparing the result to lavastorm to make sure it matches (while teaching myself python lol)

     

    Thanks Adrian

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    I'm sorry to hear about the issues you are facing. Perhaps you could use the attached custom node to simplify the conversion of the values in a selected field to a formatted string.

    The .lna contains a normal Composite node that you could copy from one edit session tab to the one containing the .lna under test, or you could convert it to a library node, if desired, and then insert it from the node library.

    We would like to understand the errors you are getting with the imported brgs as we have tried to streamline the conversion process as much as possible so that existing data flows will generally run without modification once they have been imported.

     

     

    Attached files

    Convert Double to String_342--share - 3 Jan 2020.lna

     

    0
    Comment actions Permalink
  • Avatar
    Geoff

    the most common error is:

    Node execution terminated while processing data by error: Expect string or unicode argument

    Error code: lae.node.executionTerminated

    Raised on: Jan 3, 2020 at 10:59:41 AM

     

    the graph uses trim() in many places where there is a date, or number, etc imported..looking for blanks, were are converting to nulls in Lava, it has no issue, in DS, its causes an error (since its already a date/double/etc whereas lava has it as unicode)

    the transform nodes were used to convert to the correct data type, check for blanks/nulls, filter, etc all in one... So I need to replace with modify fields, transform, filter, etc

    but the main reason for checking every node is because of the importance of the data, inaccuracies could be very costly. So I need to do this anyway.

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    What type of nodes are being used to import the data (e.g. CSV/Delimited, Excel File, etc) when you are getting the errors due to the data type not being unicode?

    0
    Comment actions Permalink
  • Avatar
    Geoff

    Excel.

    its automatically setting the date and double formats instead of everything being unicode.

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Have you tried setting the Excel File node's 'DetectFieldTypes' property to False? This causes the Excel File node to output all fields as the default type, which defaults to unicode.

    0
    Comment actions Permalink

Please sign in to leave a comment.