NUMERIC TRANSFORMATION

Six Stars

NUMERIC TRANSFORMATION

Hi All,

 

 Would like to ask a strategy for conversion of data, I have a numeric 12, 2 from my source file then when comes to redshift I have a numeric 12, 4. I want to upload the actual data from redshift without changes in data, e.g I have a 20.19 data but when I try to upload it on redshift it becomes 20.1899, instead of 20.1900. Now the question is what is the best way to get my sample result into redshift, I have pallettes of file > tmap > redshift

 

Thank you!


Accepted Solutions
Six Stars

Re: NUMERIC TRANSFORMATION

Check your schema field types.  There could be something in your component schemas changing the datatype.

 

For non-integers Talend supports Double, Float, and BigDecimal.  They compute differently and they round differently.  The introduction of the database field types (as is the case in ETL) will also complicate matters because the Talend input/output components must to handle that conversion as well.

 

I generally use BigDecimal and have no issues with the way BigDecimal performs its rounding.  However, you can try using tConvertType which handles a lot of data type conversions rather gracefully.  You can also round your numbers manually using a tMap or tJavaRow with some simple Talend/Java functions.  Talend has some functions for handling numbers in its 'Mathematical' category.  Not many for rounding, but you can also use Java functions:

 

 

Math.round()
Math.ceil()
Math.floor()

For your purposes, something like this in a tMap should handle it:

 

Math.round(row1.numField * 100.0) / 100.0)
Six Stars

Re: NUMERIC TRANSFORMATION

@PaulyWally, thanks for your reply, for this I resolved it by adding precision to 4 in to my Tmap, also I note your solution.

 

Thank you!


All Replies
Six Stars

Re: NUMERIC TRANSFORMATION

Check your schema field types.  There could be something in your component schemas changing the datatype.

 

For non-integers Talend supports Double, Float, and BigDecimal.  They compute differently and they round differently.  The introduction of the database field types (as is the case in ETL) will also complicate matters because the Talend input/output components must to handle that conversion as well.

 

I generally use BigDecimal and have no issues with the way BigDecimal performs its rounding.  However, you can try using tConvertType which handles a lot of data type conversions rather gracefully.  You can also round your numbers manually using a tMap or tJavaRow with some simple Talend/Java functions.  Talend has some functions for handling numbers in its 'Mathematical' category.  Not many for rounding, but you can also use Java functions:

 

 

Math.round()
Math.ceil()
Math.floor()

For your purposes, something like this in a tMap should handle it:

 

Math.round(row1.numField * 100.0) / 100.0)
Six Stars

Re: NUMERIC TRANSFORMATION

@PaulyWally, thanks for your reply, for this I resolved it by adding precision to 4 in to my Tmap, also I note your solution.

 

Thank you!