Using replaceAll expression for two kinds of values

[Anonymous]

Hello,

I have 2 kinds of values that I need to load on one column and load only the specific number needed. The values are:

Input: Refer to ABC 123-456 04/14 -> Output: 123456

Input: Refer ABC 789-123 03/09 -> Output: 789123

The input values having different  int/int (which means the date, but it is not important) .

I have tried to use row1.columnName.replaceAll("Refer to",""), but I still have the int/int values and also not working with the values  "Refer ABC 789-123 03/09". I'm finding a way to achieve this using tmap expression. Is there anyway to do that?

Thank you!

[Anonymous · Accepted solution]

Hi,

     My recommendation will be always educate the source system to send the data in easily readable format rather than sending data in a single free text.

     However to resolve your current problem, you can use the below function specified in the code editor.

(row1.input_data.substring((row1.input_data.indexOf("ABC") + 4),(row1.input_data.indexOf("ABC") + 11))).replace("-", "") 

      The job flow and output is as shown below.

 

If the answer has helped to resolve your query, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Thank you @nthampi. And I will note that for the future tasks. Anyway, I just want to ask if how can I put an "if" condition on that expression, so that expression should run if only the values matches what is on expression, some values are different, so it will give an error data too long, i think.

[Anonymous]

Hi,

      You can add the conditions as long as it confirms to java language syntax. 

       If you would like to create complex match logic using custom java code (with multiple if - else conditions), you can use tjavarow component instead of tmap for better readability.

Warm Regards,

Nikhil Thampi

[Anonymous]

I have the condition on my tmap expression and it works well on  test as I'm changing the value on the expression editor. But, when I tried to test it, If the value of the column is not an address and meets the refer and refer to condition all are okay, but if did not met the condition I got truncation error on the column because the values of the column is too long. The size of my data is just char(6). My question is, how can I make another condition that if did not meet the condition, just put a  value "NO ADD" instead of the address value?

row1.address.equals("Refer or Refer to") ? row1.address: (row1.input_data.substring((row1.input_data.indexOf("ABC") + 4),(row1.input_data.indexOf("ABC") + 11))).replace("-", "") 

Thank you!

[Anonymous]

Hi,

     If string length is an issue, I would recommend to add an additional condition to check the length of the string (you can use java functions for this one) and if the length is satisfying your condition, then you can go ahead. It will be basically nested if conditions which can be done either using tmap condition check or in tjavarow (by writing if else conditions).

     I would suggest you to try the if else creation yourself as it will help you to learn the tricks in tmap/tjavarow.

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

Warm Regards,

Nikhil Thampi