Converting String Values that are numeric into Decimal/Currency with a Reject File

[Anonymous]

Hello,

I have an Input file that I am dealing with a lot of bad numeric values in them. For instance I have values in it like "500.00/" "17.5.00" "01.00" "12.5.00". These values are suppose to be a Dollar Amount. Without knowing the actual Dollar amount of some of these values, I want Talend to reject them, so they don't end up in the output file. Is there a component in Talend than can Try to Convert a String value that has numeric data into a decimal/currency value to get these exact values into a reject file.

The code in SQL would be TRY_CONVERT(decimal,REPLACE(amount,',','')).

Not sure if the same logic can be applied in Talend, but not trying to bring in a DB Connection and a Query to get these values into a Reject File if I don't have too.

Please advise,

Thanks,

Andrew

[DataTeam1]

You can try to achieve it creating your custom function

Here you can find  how to create custom function: https://community.talend.com/t5/Design-and-Development/Create-a-user-routine-and-call-it-in-a-Job/ta...

Here you can find how to check if string is a proper number in java: https://www.baeldung.com/java-check-string-number

[Anonymous · Accepted solution]

Hi Andrew,

    Could you please try below job flow?

I was able to separate good and bad records. Please refer the tjavarow component below.

The code used is:-

output_row.data = input_row.data;

 try{
        Float.parseFloat(input_row.data);
        output_row.flag="Y";
    }catch(NumberFormatException e){
        output_row.flag="N";
    }

The tMap filter logic is as shown below.

Hope I have answered your query 🙂

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 🙂

[Anonymous]

Hey @nthampi ,

Can you post your tLogRow Output Basic Settings? I'm not sure how you separated the Good Values from the Bad values in the Console. For me they're all printing to the same line

Thanks,

Andrew

[Anonymous]

Hi Andrew,

     It has nothing to do with your tLogrow. If everything is coming at same line means, either your try catch is not working or output expression in tMap has some issues.

    Could you please print the output using a tLogrow before tMap and after tjavarow to check whether flag value is getting set correctly?

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 🙂