[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