Round value to two decimal place

Highlighted
Seven Stars

Round value to two decimal place

Hello,

 

I have the following query which update a column :

 

UPDATE TABLE1 SET COLUMN1= CAST(COLUMN2 AS DECIMAL(15,2)) - CAST(COLUMN3 AS DECIMAL(15,2));

 

I want to apply the same thing in my tmap.

 

For info, column2 and column3 is of type string in my file.

 

COLUMN2COLUMN3EXPECTED RESULT
1710 171.00
016 136.07   -16136.07
014 718.00   -14 718.00
0296.61 -296.61
01 430.76   -1 430.76
46 040.94  0 46 040.94  

 

Can you advice please?

 

Thank you in advance.

 

Best regards,

asadasing


Accepted Solutions
Sixteen Stars TRF
Sixteen Stars

Re: Round value to two decimal place

As column2 and column3 are Strings, you need to convert to Double before the operation, giving something like:

Double.parseDouble(row1.column2) - Double.parseDouble(row1.column3)

But, as you want 2 digits on the decimal part, you need to convert the result to String:

String.format("%.2f", (Double.parseDouble(row1.column2) - Double.parseDouble(row1.column3)))

Let me know if it works as expected.

 

 


TRF

View solution in original post


All Replies
Sixteen Stars TRF
Sixteen Stars

Re: Round value to two decimal place

As column2 and column3 are Strings, you need to convert to Double before the operation, giving something like:

Double.parseDouble(row1.column2) - Double.parseDouble(row1.column3)

But, as you want 2 digits on the decimal part, you need to convert the result to String:

String.format("%.2f", (Double.parseDouble(row1.column2) - Double.parseDouble(row1.column3)))

Let me know if it works as expected.

 

 


TRF

View solution in original post

Forteen Stars

Re: Round value to two decimal place

@asadasing,can you try with Math.round() ?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Seven Stars

Re: Round value to two decimal place

Hello TRF,

Thank you for your quick reasponse.
I checked in notepad++, It works but not in excel.

I guess in the next processing it will consider the decimals.

Best regards,
asadasing
Sixteen Stars TRF
Sixteen Stars

Re: Round value to two decimal place

OK.

Don't forget to mark your case as solved.


TRF
Seven Stars

Re: Round value to two decimal place

Hello,

I have applied your solution and encountering some issue.

See url please : https://community.talend.com/t5/Design-and-Development/Format-value-to-two-decimal-places-without-ro...

Thank you.
Regards,
Asadasing
Five Stars

Re: Round value to two decimal place

Hi,

what if i want to convert the values of float which is a string to an integer value.

Ex: My value is a string - 1.00000000, and i want to convert into Integer as 1.

 

I am trying the below expression, but not able to achieve the requirement.

Integer.parseInt(Double.parseDouble(row16.stringvalue)).IntValue() .

 

First converting the string to double and then converting to Integer.

 

Please suggest a solution, Thanks 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now