topic Re: [resolved] tMap - update only if value is non-Null? in Talend Studio

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

soowork — Thu, 14 Nov 2013 14:49:01 GMT

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

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

soowork — Thu, 14 Nov 2013 15:04:02 GMT

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.

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

soowork — Thu, 14 Nov 2013 20:08:06 GMT

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

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

Anonymous — Thu, 11 Feb 2016 09:02:48 GMT

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?

Anonymous — Thu, 11 Feb 2016 09:54:24 GMT

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?

Anonymous — Thu, 11 Feb 2016 09:55:51 GMT

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

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

Anonymous — Thu, 11 Feb 2016 09:57:43 GMT

see screenshot.
Regards,
Amol

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

Anonymous — Thu, 11 Feb 2016 10:00:47 GMT

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 , but with the change, it works!

Thanks a lot for your help!

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

Anonymous — Thu, 11 Feb 2016 10:06:38 GMT

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

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

_AnonymousUser — Fri, 22 Jul 2016 07:16:36 GMT

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

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

Anonymous — Fri, 22 Jul 2016 09:22:56 GMT

Hi Dorthy,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

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

_AnonymousUser — Fri, 22 Jul 2016 10:11:40 GMT

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