How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Seven Stars

How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

I'm getting a Java String Error when converting an Amount of "0000000000006" to 00.06 (6 cents) in tMap Expression.

 

Can anyone let me know what is wrong with my SubString Java String Method to allow to pass this value. I already got rid of the "0000000000000" values in a tFilterrow before I pass these values through a tMap.  I just need to be able to pass values with amounts less than 10 cents.

 

Exception in component tMap_1 (HHV_Contributions)
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)

 

 

Variable=NewAmount

row1.AMOUNT.replaceFirst("^0+(?!$)", "") 

 Expression (String Method)

 

Var.UpdatedAmount.substring(0,Var.UpdatedAmount.length()-2)+"."+Var.UpdatedAmount.substring(Var.UpdatedAmount.length()-2)

 Thanks,,

 

Andrew


Accepted Solutions
Employee

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Hi Andrew,

 

     Please refer the screenshot below if you would like to follow your earlier method.

image.png

 

image.png

 

The tMap Screenshots are:-

image.png

 

The functions are as below:-

 

First variable ->      row1.input.substring(0,row1.input.length()-2) 
Last variable ->      row1.input.substring(row1.input.length()-2,row1.input.length()) 
Output          ->       Var.first.replaceFirst("^0+(?!$)", "") +"."+Var.last 

Hope you got your answer now :-)

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post


All Replies
Eleven Stars

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

did you try

Long.parseLong("0000000000006")/100.00  --- > 0.06

 

or in correct string format 

 

String.format("%05.2f", Long.parseLong("0000000000006")/100.00)  --> 00.06

 

or

 

String.format("%014.2f", Long.parseLong("0000000000006")/100.00) --> 00000000000.06

 

Hope this will help.

Regards
Abhishek KUMAR
Employee

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

@AndrewSmith1182 

 

Pitching in since Andrew requested help through chat.

 

Hi Andrew,

 

   Abhishek's solution is absolutely correct. Please refer the screenshots below for details.

 

image.png

image.png

 

The tMap will be as shown below.

image.png

 

Long.parseLong(row1.input )/100.00 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Seven Stars

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Abhishek or Nikhil

 

How would I add that syntax to my current String Method I mentioned in my last comment 

Long.parseLong(row1.input )/100.00 

 

Variable=NewAmount

row1.AMOUNT.replaceFirst("^0+(?!$)", "") 

 Expression (String Method)

 

Var.UpdatedAmount.substring(0,Var.UpdatedAmount.length()-2)+"."+Var.UpdatedAmount.substring(Var.UpdatedAmount.length()-2)+Long.parseLong(Var.UpdatedLength)/100.00

 Would the red text I added to the expression do the trick? 

I feel like if I add that current logic that my outputs that aren't "0000000000006" would be skewed like "0000000001600" would turn into $00.16 instead of $16.00 am I right?

My Output in my tMap has to stay a "String" data type and not a "Double" data type.

 

Please advise,

 

Thanks

 

Andrew

Eleven Stars

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

hi @AndrewSmith1182 

 

you need not use any variable or the expression which you were using before ,

the expression nentioned above provided are enough 

 

000000001600 will return 16.00

000000000006 will return 0.06

 

 

if this is not what you want  i am not able to understand your question

 

Regards
Abhishek KUMAR
Employee

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Hi Andrew,

 

     Please refer the screenshot below if you would like to follow your earlier method.

image.png

 

image.png

 

The tMap Screenshots are:-

image.png

 

The functions are as below:-

 

First variable ->      row1.input.substring(0,row1.input.length()-2) 
Last variable ->      row1.input.substring(row1.input.length()-2,row1.input.length()) 
Output          ->       Var.first.replaceFirst("^0+(?!$)", "") +"."+Var.last 

Hope you got your answer now :-)

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post

Seven Stars

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Hey @nikhilthampi

 

With the latest string methods you gave me 

row1.AMOUNT.substring(0,row1.AMOUNT.length()-2)

row1.AMOUNT.substring(row1.AMOUNT.length()-2,row1.AMOUNT.length())

Var.First.replaceFirst("^0+(?!$)", "") +"."+Var.Last.

 

My output amount values are returning values like 25, 5,15 etc. I would want the value to include .00 at the end so the actual value would be 25.00 5.00 15.00. The only values that are giving the cents are the ones that don't end with a .00. So 3.25 is appearing and 7.50. These values are correct. Just need to allow for .00 not to get shaved off at the end.

 

Please advice,

 

Thanks,

 

Andrew

Employee

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Hi Andrew,

 

    Could you please print the output of each section of that String concatenation and see the results? I think its some problem at the setup at your end.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Seven Stars

Re: How to convert a string value of format “$$$$$$$$$$$cc" "0000000000006" for example would convert to string 00.06 with expression builder in tMap. Need Help with String Method in Java Script

Hey Nikhil,

 

Fixed the issue on my end, gotta stop using Excel as my File Viewer it tends to manipulate my output file data especially numbers.

 

This Issue is resolved,

 

Thanks for all the help,

 

-Andrew

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog