[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

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 2

Part 2 of a series on Context Variables

Blog

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