How do i replace 2.000- to 2.000

[gouravd_7]

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 

[Anonymous]

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.

 

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

[gouravd_7]

Hi Nikhil,

Thanks for the quick response. Really appreciate it.

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

1JPG.JPG

[Anonymous]

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

[gouravd_7]

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

[Anonymous]

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.

      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

[gouravd_7]

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. 

2.JPG

[Anonymous]

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 

[gouravd_7]

Hi Nikhil,

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

We cant change the target db from our side.

Thanks,

Gourav Dubey

[TRF]

If the target is an int, use this one:

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