Inserting/Updating a field values based on the updated or inserted values on other column.

Highlighted
Seven Stars

Inserting/Updating a field values based on the updated or inserted values on other column.

I need to populate the specific values that I extracted from one of the column in a table

The value in these fields is to be populated, as the name suggests, when the record is created and updated or even deleted respectively.
So when loading a record from the source into the destination I need to check (based on the destination's primary key) if the record exists in the number columns or not. If it does not then I need to put that values on remarks column, if the values is still exists I need to update or insert it on the new_number column and when the time comes that the data has been deleted, I also need to put it on remarks column.

 

I'm extracting the values and get the number on address column using this expression:

 

row2.ADDRESS.contains("%REFER%") ? row2.ADDRESS.trim(): ((row2.ADDRESS.substring(row2.ADDRESS.indexOf("ABC") +4, Math.min(row2.ADDRESS.indexOf("ABC") + 11, row2.ADDRESS.length())).replace("-","").replaceAll("\\D","")))

 

I used a lookup and try to get the right result by doing a left join and link that expression on the key. 

 

But the problems is:

 

image.png

 

1. If you will noticed on the screenshot instead of only those 6 numbers which are the valid values for this column should be extracted, it is also extracted some other values which should not be there.

 

2. Since 012006 is the only number exists on first column, that is the only values should be there and the others should be on remarks column. (if exists put values, if not,put on remarks).

 

Hope there's someone there who can tell me what is the other things I need to do here and or help me to get and have only the values I need and not those values that are not needed on that 2nd column.

 

Thanks

 


Accepted Solutions
Seven Stars

Re: Inserting/Updating a field values based on the updated or inserted values on other column.

I got it already, please disregard this. I solved it by comparing the integer before UPDATE.

All Replies
Seven Stars

Re: Inserting/Updating a field values based on the updated or inserted values on other column.

I got it already, please disregard this. I solved it by comparing the integer before UPDATE.

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