Data360 Analyze

Hi,

Just checking if anyone had the same issues when handling arithmetic including large floating numbers.

The concern here is when the number gets beyond 10 million, and the data viewer is showing up with the scientific notion, such as "1.56893812E8" instead of "156893812".

Qn: Can this be configured anywhere to show actual numbers? This actually comes from specific use cases which requires the actual number for visual inspection.

The field with type double is accurate when passing through data flow for computations - but I'm also considering exporting to csv, excel etc to have the desired visual representation.

Thanks!

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

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 

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).

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.

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

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

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

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

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.

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?

Excel.

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

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.