topic Re: Convert String to Decimal and Error Handling in Talend Studio

Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 14:21:16 GMT

Hi All,

I want to convert Data of One of My Column which is holding Value like 7.13084E17. E is nothing but 0's. Value is like 7.13084000000000000.Now I have to Convert it to Decimal. In Output Table Column is in Number(30). Now I am converting it to Decimal Something Like This in variable Port o_row1.column.

Double.parseDouble(row1.column)==0?0ouble.parseDouble(row1.column). Now using this Same in tmap Pass and Fail Table as Var.o_row1.column>=-1.00(Pass) and Var.o_row1.column<=-1.00. But Compilation error is coming saying can't convert from Double to BigDecimal. My Pass Table of tmap o_row1.column is BigDecimal

@rhall, @TRF, @manodwhb, @vboppudi, @TRF

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 14:35:33 GMT

Now changing to BIGDECIMAL in the Variable Port Part getting error in the Validation Part

new BigDecimal(row1.column)=Variable Port for Validation(Big Decimal)

new BigDecimal(row1.column)==0?0:new BigDecimal(row1.column)=Output Port to Pass Table of tmap(Big Decimal)

In the expression Giving Area of Pass and Fail Table tmap

giving like this

Variable Port for Validation(Big Decimal)>=-1

Variable Port for Validation(Big Decimal)<=-1

But getting error as saying Incompatible operand types BigDecimal and int in Output Port to Pass Table of tmap.

Re: Convert String to Decimal and Error Handling

Anonymous — Fri, 27 Apr 2018 14:37:50 GMT

You need to show us your tMap with your configuration. This is down to you not using a BigDecimal class. Now we need to know if you are getting the values as Strings with the scientific notation or numbers. We also need to know whether you are trying to use boolean logic with your pass and fail values (should you be using a boolean type or this). Take a look here https://docs.oracle.com/javase/7/docs/api/java/math/BigDecimal.html

Something that is certainly wrong (and you've not yet realised) is this.....

Var.o_row1.column>=-1.00(Pass) and Var.o_row1.column<=-1.00

>= -1 and <= -1 will be true for -1. You do not want that.

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 14:48:43 GMT

I have attached the settings.Fail setting is Var.v_TWEET_ID_CONVERSION<=-1

pass condition is Var.v_TWEET_ID_CONVERSION>=-1

o_TWEET_ID.JPG
o_TWEET_ID_SETTINGS.JPG
PASS_CONDITION.JPG
tmap_pass.JPG
v_tweet_id_conversion.JPG

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 15:46:19 GMT

When I changed my o_TWEET_ID Datatype to Integer and gave this condition

row1.TWEET_ID==null || row1.TWEET_ID==""?0:Integer.parseInt((row1.TWEET_ID))

In my PASS Table of tmap the Port is Integer with Length 30. In Database the Column is NUMBER(30)

@rhall

Re: Convert String to Decimal and Error Handling

Anonymous — Fri, 27 Apr 2018 15:54:34 GMT

OK, you need to simplify this and show us an example of something simple that fails. There is too much for me to look at and to try and work out.

By the way, your logic ....

Fail setting is Var.v_TWEET_ID_CONVERSION<=-1 
pass condition is Var.v_TWEET_ID_CONVERSION>=-1

...is still broken. if Var.v_TWEET_ID_CONVERSION is -1 it will meet BOTH of those conditions. Just remove the = from one of them.

Re: Convert String to Decimal and Error Handling

vboppudi — Fri, 27 Apr 2018 15:55:29 GMT

Hi Abhi,

Please provide some sample input records and expected output.

Regards,

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 16:04:46 GMT

@vboppudi,

Please find it

6.84136E+17

6.917E+17

Like this. Basically Source is String is String and in target Datatype is NUMBER(30). I want to convert it to Number as well as want to handle Error if comes which I am doing using a Variable Port.

Re: Convert String to Decimal and Error Handling

vboppudi — Fri, 27 Apr 2018 16:12:20 GMT

What is the error criteria? is your source is excel?

6.84136E+17 is it valid ? if valid what is the output you are expecting?

6.917E+17 is it valid ? if valid what is the output you are expecting?

Please give some sample data for invalid.

Regards,

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 16:15:20 GMT

Yess Both are Valid.Coming from .csv and .txt. In Output Table Datatype is NUMBER(30).

InValid Record is -1.If any Value comes -1 or less than -1 then it will go ERROR Table.

6.84136E+17 output Port will be like

if row1.TWEET_ID==null?0:row1.TWEET_ID

@vboppudi

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 16:31:45 GMT

@vboppudi,

Let me know if any details required. I am also trying

Re: Convert String to Decimal and Error Handling

vboppudi — Fri, 27 Apr 2018 16:58:22 GMT

Hi Abhi,

Are you looking for this?

Regards,

Re: Convert String to Decimal and Error Handling

abhi90 — Fri, 27 Apr 2018 17:47:33 GMT

@vboppudi,

Yes in Database ValidData should go with E. But how did u managed to do this?

Re: Convert String to Decimal and Error Handling

vboppudi — Fri, 27 Apr 2018 17:52:40 GMT

Use the following settings in tMap.

Regards,