Update a column values base on the vakues of another column in the same table using tmap

Seven Stars

Update a column values base on the vakues of another column in the same table using tmap

Hello,

I cannot deternine on how can I uodate the values of a column base on the values from another colum on the same table using talend tmap.

in typical mysql I can make something like
update tbl set col2 = case
when col1 = 'Nice' then 'N',
when col1 = 'bad' then 'B',
when, col1 = 'Good' then 'G' ;
End

How can I do this using tmap, any help is realky and highly appreciated. Cheers

Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

You have to use ternary notation for the target field in your tMap:
"Nice". equalsIgnoreCase(row1.col1) ? "N" : "bad".equalsIgnoreCase(row1.col1) ? "B" : "Good".equalsIgnoreCase(row1.col1) ? "G" : "?"

Should work.

TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

You have to use ternary notation for the target field in your tMap:
"Nice". equalsIgnoreCase(row1.col1) ? "N" : "bad".equalsIgnoreCase(row1.col1) ? "B" : "Good".equalsIgnoreCase(row1.col1) ? "G" : "?"

Should work.

TRF
Seven Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Thanks for the response, but what is tenary notation? Should I just put this on the expression for the target column that should be updated or?

Seven Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

And actually I want to base the values of the column that needs to  be updated base on the values from another column on the same table. Is this will work with that?

Highlighted
Fifteen Stars TRF
Fifteen Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Yes, just put the expression for the target field.
The ternary notation or operator is here to build expressions for the right part of a variable setting where you cannot write an if-then-else expression. That's exactly the situation for a tMap.
You can find a lot of information if you search on Google or other. Here is an example https://alvinalexander.com/java/edu/pj/pj010018

TRF
Seven Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Thank you very much!

Fifteen Stars TRF
Fifteen Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

You're welcome

TRF
Five Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Hi all,

 

In my file i will get two columns Column_A & Column_B such that only one of the value is NULL

I need to map the above column values to my column in db called VALUE.

 

I am using ternary operation in Tmap such that i need to populate only one the value from two columns giving preference to column_A.

 

My Logic:

"".equalsIgnoreCase(row19.Column_A)?("".equalsIgnoreCase(row19.Column_B)?null:row19.Column_B):row19.Column_A

 

Above logic is not working properly, when Column_A is null, it should be populating Column_B which is not NULL, but it is populating as NULL.

 

Please help me.

 

Eleven Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Null and empty string is two diff stuff so null doesn’t match to “”.

(Col.a == null || “”.equal...)?( Col.b == null || “”.equal...)? ....
Regards
Abhishek KUMAR
Five Stars

Re: Update a column values base on the vakues of another column in the same table using tmap

Thanks for the suggestion Abshishek,

 

Below is my logic in Tmap:

(row19.questionAnswerFreeText == null || "".equalsIgnoreCase(row19.questionAnswerFreeText))?( (row19.questionAnswerText) == null || "".equalsIgnoreCase(row19.questionAnswerText)

?null : (row19.questionAnswerText)) : row19.questionAnswerFreeText

 

Still its not working.

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