topic Re: tmap expression filter in Talend Studio

tmap expression filter

Anonymous — Wed, 12 Jun 2019 13:48:33 GMT

Hello

I want to add a condition in expression filter. I have a fact table and a lookup table. Conditions I want to apply are when

Fact.Filter1 == LKP.Filter1 and for Filter2 in both tables could be either equals to each other (Fact.Filter2 == LKP.Filter2) or Lookup Filter2 is null (LKP.Filter2 == null) or Fact Filter2 is null (Fact.Filter2 == null) or both are null. So condition on Filter1 is a must but then for Filter2 if any one of those condition is satisfied, then the record should go in table. I am unable to formulate this in tmap expression filter.

I tried

Fact.FILTER1.startsWith(FILTER1)&&(Fact.FILTER2.equals(LKP.FILTER2)||Relational.ISNULL(LKP.FILTER2)||Relational.ISNULL(Fact.FILTER2))

But this statement fails when Fact.FILTER2 == null

How can I solve this issue?

Regards

Priya

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 14:24:29 GMT

Hi Priya,

The below method should work in your case.

Relational.ISNULL(row1.filter2) || Relational.ISNULL(lookup.filter2) || row1.filter2.equals(lookup.filter2)

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 14:55:35 GMT

Thank you @nthampi for your reply. But I cant join fact and lookup table like you suggested. If you see my code, I am joining with the help of pattern (Fact.FILTER1.startsWith(FILTER1)). How can I accomodate that?

Regards

Priya

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 15:08:15 GMT

Ok. In that case, you can add this condition also in the expression area. But please make sure that any string condition comes after Null check so that you will not get any Null pointer exception.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 15:14:04 GMT

Thank you @nthampi

So my expression filter will be

(Relational.ISNULL(row1.filter2) || Relational.ISNULL(lookup.filter2) || row1.filter2.equals(lookup.filter2))&&row1.filter1.startsWith(lookup.filter1))

I am correct?

Regards

Priya

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 15:23:37 GMT

Hi Priya,

Slight change since we have to make null check for all columns.

Relational.ISNULL(row1.filter2) || Relational.ISNULL(lookup.filter2) ||  (row1.filter2.equals(lookup.filter2)&& !Relational.ISNULL(row1.filter1) 
                                    && !Relational.ISNULL(lookup.filter1) 
                                    && row1.filter1.startsWith(lookup.filter1))

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tmap expression filter

Anonymous — Wed, 12 Jun 2019 19:48:04 GMT

Thank you so much! this worked..