[resolved] tMap - update only if value is non-Null?

Six Stars

[resolved] tMap - update only if value is non-Null?

Is there a way, using tMap, to say that I only want to update a value in the output table if the matching input value is not NULL?
If the input value is non NULL, then I want to update the column. If the input value is NULL, I want to leave the existing value alone. This will vary from row to row.
I suspect this is quite simple, but I am struggling figuring it out. TIA.
Input
ID Value
1 cat
2 NULL
4 fish
Existing Table
1 kitten
2 snake
3 koala
4 turtle
5 dog

Result
1 cat
2 snake
3 koala
4 fish
5 dog
Six Stars

Re: [resolved] tMap - update only if value is non-Null?

Ah, ok, I think I have it figured out.
Basically have my tMap output be
NewData.field == NULL ? ExistingTable.field : NewData.field
See the screenshot attached.
Six Stars

Re: [resolved] tMap - update only if value is non-Null?

(I should have said null instead of NULL. doh.)
One Star

Re: [resolved] tMap - update only if value is non-Null?

hi!
I have used a similar solution, but when the lookup returns 0 rows, the job throws a NullPointer Exception at ExistinTable.field reference.  Any idea about this?
Thanks in advance

Re: [resolved] tMap - update only if value is non-Null?

Hi lgtleon,
I have also tested above solution. It works perfectly fine in both case.
case I : when lookup row returns non zero row.
case II : when lookup row returns  zero row.
See screenshot. ( Note that I have created job according to first post and included case II )
 

Re: [resolved] tMap - update only if value is non-Null?

Hi,
I am not able to upload screenshot.
Regards,
Amol

Re: [resolved] tMap - update only if value is non-Null?


see screenshot.
Regards,
Amol
One Star

Re: [resolved] tMap - update only if value is non-Null?

Hi again!
I have changed the expression used to verify null values
Old version => ((!((Integer)out8.idOrgPub).equals(row_existing.idOrgPub)) && (out8.idOrgPub != 0)) ? out8.idOrgPub : row_existing.idOrgPub 
New version =>Relational.ISNULL(out8.idOrgPub) ?row_existing.idOrgPub: out8.idOrgPub 
I can´t see the difference Smiley Sad, but with the change, it works!

Thanks a lot for your help!

Re: [resolved] tMap - update only if value is non-Null?

Hi lgtleon,
Nice to see that you got a solution. 
And I have used below expression for a testing:
NewData.field == NULL ? ExistingTable.field : NewData.field
Regards,
Amol
One Star

Re: [resolved] tMap - update only if value is non-Null?

Hi,
I want to get the first non null value from a column using tmap. PLs suggest a solution.
Moderator

Re: [resolved] tMap - update only if value is non-Null?

Hi Dorthy,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] tMap - update only if value is non-Null?

Here's my scenario.
I should take the first non null value.
I'm doing a lookup, where for one column i can have more than one values. 
Say for eg: (str1,str2,str3).-> In this case the value to be considered should be str1.
       case2: (null,str2,str3) -> In this case my value should be str2.
I need to handle this situation inside tmap where i should get the first non null value for that particular column.
Regards,
Dorthy

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog