How do i replace 2.000- to 2.000

Six Stars

How do i replace 2.000- to 2.000

Hi Team,

 

PLease help me to replace the numbers in table like 2.000- to -2.000 in talend job.

Please find the attached screenshot of the job.

 

Thanks,

Gourav Dubey 


Accepted Solutions
Six Stars

Re: How do i replace 2.000- to 2.000

Hi TRF, 

 

Thanks, I was able to resolve the issue with those steps however am getting another issue with Float to string conversion in tMap but I will mark this case as resolved as my first query mentioned over here was resolved with above steps. 

 

Thanks alot Smiley Happy

 

Do let me know incase you have float to string conversion code as the one ,I have been trying is not working.

 

Thanks,

Gourav Dubey


All Replies
Employee

Re: How do i replace 2.000- to 2.000

Hi,

 

    My first query will be do you have any data quality issues in your source DB table? Ideally the data cleansing should always happen from source rather than from an interim layer.

 

    Now, if the source is not ready to cleanse it and will continue to send data in wrong format like "2.000-" (I assume it is a string data type) then first thing you will have to do is to pass the data to a tmap/tjavarow.

image.png

 image.png

 

 

 

The code I used is as shown below. If needed, you can convert the output string value to other data types once the cleansing is complete.

 

row1.input.contains("-")?"-"+row1.input.replaceAll("-", ""):row1.input 

If the reply has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Nikhil,

 

Thanks for the quick response. Really appreciate it. Smiley Happy

 

My Source column is string and target column is float so the above code is giving me error.

 

Please find the attached screenshot.

 

Thanks & Regards,

Gourav Dubey

Employee

Re: How do i replace 2.000- to 2.000

Hi Gourav,

 

     Please use tconverttype after tmap to convert the value from String to Float once the cleansing is complete.

 

     Below link will give the details about this component.

 

https://help.talend.com/reader/hm5FaPiiOP31nUYHph0JwQ/GDoeGgb~qKpyQ_84gB37WQ

 

     If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Nikhil,

 

tConvertype doesnt  have output so we cant use it after Tmap and before tMap , have tried using it multiple times but it doesnt converts anything to my bad luck.

 

Please help me with some other solution.

 

Thanks,

Gourav Dubey

Employee

Re: How do i replace 2.000- to 2.000

Hi Gourav,

 

     I was able to do it. You will have to add the schema details for both input and output.

 

     I didn't worry about the precision of my output but you can handle it also f you want.

 

image.png

 

 

      I would suggest you to go through sample scenarios mentioned in Talend Help link where this component is being used. It will help you to get better familiarity of the component.

 

     If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Nikhil,

 

Please find the below log after following the above process 

Starting job quickcheck at 18:48 14/11/2018.

[statistics] connecting to socket on port 3359
[statistics] connected
Exception in component tMSSqlInput_1 (quickcheck)
java.sql.SQLException: Value 2.000- cannot be converted to REAL.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:752)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getFloat(JtdsResultSet.java:683)
at sga_data_lake.quickcheck_0_1.quickcheck.tMSSqlInput_1Process(quickcheck.java:12746)
at sga_data_lake.quickcheck_0_1.quickcheck.runJobInTOS(quickcheck.java:16732)
at sga_data_lake.quickcheck_0_1.quickcheck.main(quickcheck.java:16390)
[FATAL]: sga_data_lake.quickcheck_0_1.quickcheck - tMSSqlInput_1 Value 2.000- cannot be converted to REAL.
java.sql.SQLException: Value 2.000- cannot be converted to REAL.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:752)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getFloat(JtdsResultSet.java:683)
at sga_data_lake.quickcheck_0_1.quickcheck.tMSSqlInput_1Process(quickcheck.java:12746)
at sga_data_lake.quickcheck_0_1.quickcheck.runJobInTOS(quickcheck.java:16732)
at sga_data_lake.quickcheck_0_1.quickcheck.main(quickcheck.java:16390)
[statistics] disconnected
Job quickcheck ended at 18:49 14/11/2018. [exit code=1]

 

 

Also, please find the attached screenshot of the job after 6 rows getting same error again. 

Employee

Re: How do i replace 2.000- to 2.000

Hi,

 

    It seems you are reading the data in tMSSQLInput with wrong data type. Could you please cross check the data type of source table and align your schema in Talend input component accordingly?

 

   I believe you need to read that column as a string. Could you please cross checkthe DDL of that source table and the source table schema you have created in tMSSQLInput component?

 

Warm Regards,

 

Nikhil Thampi 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Nikhil,

 

In source its varchar (100) and in target its INT. 

We cant change the target db from our side.

 

Thanks,

Gourav Dubey

Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

If the target is an int, use this one:

Integer.parseInt(
    (row10.input.contains("-") ? "-" + row10.input.replaceAll("-", "") : row10.input).replaceAll("\\..*$", "")
) 

TRF
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Team,

 

I am still getting the same error 

 

 

[statistics] connecting to socket on port 3802
[statistics] connected
Exception in component tMSSqlInput_1 (quickcheck)
java.sql.SQLException: Value 2.000- cannot be converted to REAL.

 

Thanks & Regards,

Gourav Dubey

Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

The expression mut be placed int o the tMap.

The job design must look like this:

tMSSqlInput --> tMap --> tMSSqlOutput

As the erro come from your tMSSqlInput_1 component, can you share its Settings?


TRF
Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

The expression mut be placed int o the tMap.

The job design must look like this:

tMSSqlInput --> tMap --> tMSSqlOutput

As the error come from your tMSSqlInput_1 component, can you share its Settings?


TRF
Employee

Re: How do i replace 2.000- to 2.000

Hi Gourav,

 

    Could you please share the screenshot of the tMSSQLInput component's schema? I am interested in the datatype you have added for this column in the schema.

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Team,

 

Attaching the job so that you can cross check the same. 

Also. I did the mapping of all 113 columns again and some how the issue with input component resolved and am getting this error now

Type mismatch cannot convert  from int to string.

 

Please find the attached zip file of the job and screenshot of the error where we are getting this issue 

Employee

Re: How do i replace 2.000- to 2.000

Hi,

 

    Could you please add column names in the select query and once they are added correctly, could you please try Guess schema?

 

    I believe you have missed some column in schema which is of integer type in source table.

 

image.png

 

 

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

You must CAST from Int to String into your query by yourself.

Example: 

SELECT CAST(yourIntColumn AS VARCHAR) from yourTable

 

Edit: you must CAST the numeric fields as soon as you want them into a String field 

 

Tip: to get the table schema without having to enter a complete SELECT, enter the query as "SELECT * FROM yourTable" then click the "Guess schema" to get the schema of the table and finally, click the "Guess query" to generate the corresponding SELECT statement (this suppose your SQL Server connection doesn't use context or global variables which are setted at runtime - in this case, use a dedicated component for design with the connection element hard coded and copy/past both resulting schema and query into the component included into your job)


TRF
Six Stars

Re: How do i replace 2.000- to 2.000

Hi Team,

 

As per the instructions , I have added the columns with cast statements in the query mentioned in above mail chain still facing the same issue .

 

Thanks & Regards,

Gourav Dubey 

Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

Your tMap output is String (for the fields where you want to remove the final "-") so it doesn't make sense to try to convert to Integer.

It's your responsability to clean up your job before to say "it doesn't work".


TRF
Employee

Re: How do i replace 2.000- to 2.000

Hi Gourav,

 

      You seems to be changing at wrong area. @TRF has advised you to convert the integer columns to String within select statement and then onlyasking you to allocate to a STring data type in the schema mentioned in the tMSSQLInput component.

 

      If you have a integer data type in underlying database, you have two options.

 

a) convert them to string in SQL statement itself and pass it to string variables in the schema mentioned in tMSSQLInput component

or

b) allocate the column as integer datatype itself in both select statement and schema. If needed, convert to other data types later in your flow.

 

 

Warm Regards,

 

Nikhil Thampi

 

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do i replace 2.000- to 2.000

Hi TRF,

 

Yes , I know that its my responsibility to clean up my job ,I was just giving you the heads up that following your steps lead to me with the error. 

Actually the inputs given by you and Nikhil at the same time was confusing so , I followed both the steps.

Thanks for the response TRF.

 

 

 

Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

Anyway, use CAST if you have to translate "number" to String and use the expression I give to move the negative sign to the beginning and remove the decimal point and trailing 0 to get an integer.


TRF
Forteen Stars TRF
Forteen Stars

Re: How do i replace 2.000- to 2.000

@gouravd_7, does this help?

If so, thanks to mark your case as solved (Kudos also accepted as a bonus)


TRF
Six Stars

Re: How do i replace 2.000- to 2.000

Hi TRF, 

 

Thanks, I was able to resolve the issue with those steps however am getting another issue with Float to string conversion in tMap but I will mark this case as resolved as my first query mentioned over here was resolved with above steps. 

 

Thanks alot Smiley Happy

 

Do let me know incase you have float to string conversion code as the one ,I have been trying is not working.

 

Thanks,

Gourav Dubey

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.