Negative sign at end of the number

Six Stars

Negative sign at end of the number

Hi,

 

We have a requirement where all decimal values are  in European decimal separator and system, so the negative values are coming from file are as 76,000- 

According to european system I have made following 

 

File.JPG

 

But file is not able to read negative values, please suggest.


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Negative sign at end of the number

@pravin_sanadi, did you find this answer usefull or not?

TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Negative sign at end of the number

Hi,

 

In such a case, consider the input as a string (uncheck Advanced separator option).

After the tFileInputDelimited, add a tMap in which you define a var (let say minusFirst) with the following code:

StringHandling.RIGHT(row27.line, 1).equals("-") ? 
	"-" + StringHandling.EREPLACE(row27.line, "-", "") : row27.line 

This will move the minus sign (if any) at the begenning of the string.

Then on the output flow (right part of tMap),  define a double field (let sat signeResult), connect it to the variable minusfirst, replace "." (thousands separator) by "" and "," (decimal separator) by "," and finally cast the result to double.

Here is what the tMap should look like:

Capture.PNG

Or the output flow expression if you prefer:

Double.parseDouble(Var.minusFirst.replaceAll("\\.", "").replace(",", ".")) 

You may prefer to do something similar with a tJavaRow.

Here is the result:

[statistics] connecting to socket on port 4058
[statistics] connected
.--------.
| Input  |
|=------=|
|line    |
|=------=|
|10      |
|10.000,5|
|15-     |
|20,75-  |
'--------'

.------------.
|   Output   |
|=----------=|
|signedResult|
|=----------=|
|10.0        |
|10000.5     |
|-15.0       |
|-20.75      |
'------------'
[statistics] disconnected

Hope this helps.


TRF
Fifteen Stars TRF
Fifteen Stars

Re: Negative sign at end of the number

@pravin_sanadi, did you find this answer usefull or not?

TRF
Six Stars

Re: Negative sign at end of the number

Thanks a lot , was busy with lot of implementation so didn't get time.

Once again thanks

Six Stars

Re: Negative sign at end of the number

I tried your solution and its working when the value is -100 but when the negative value is in the "()" then it's not working.

Example :: value = (100) is equivalent to -100.
I tried this but it's not working.

StringHandling.RIGHT(row27.line, 1).equals("(") ? 
"-" + StringHandling.EREPLACE(row27.line, "(", "") : row27.line 

@TRF Can you please help me out. 

Fifteen Stars TRF
Fifteen Stars

Re: Negative sign at end of the number

Hi,

When you write:

StringHandling.RIGHT(row27.line, 1).equals("(")

this meens, the last character should be a "(" and you expect a ")", so replace "(" by ")" and let us know.


TRF
Six Stars

Re: Negative sign at end of the number

When I wrote 

StringHandling.RIGHT(row27.line, 1).equals(")") ? 
"-" + StringHandling.EREPLACE(row27.line, ")", "") : row27.line

it stop inserting data and not working at all.

@TRF can you please help me out form this issue.

Six Stars

Re: Negative sign at end of the number

Anyone is there ?
I tried this but it's not working.
please help me out from this issue.

Fifteen Stars TRF
Fifteen Stars

Re: Negative sign at end of the number

Hi,

Open a new topic with your usecase (input data and expected result).


TRF
Six Stars

Re: Negative sign at end of the number

Okay sure.

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now