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

Highlighted
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
Highlighted
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.
Highlighted
Six Stars

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

(I should have said null instead of NULL. doh.)
Highlighted
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
Highlighted

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 )
 
Highlighted

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

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

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


see screenshot.
Regards,
Amol
Highlighted
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!
Highlighted

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
Highlighted
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.
Highlighted
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.
Highlighted
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 GARTNER 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

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