tmap expression filter

[Anonymous]

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

[Anonymous]

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 🙂

[Anonymous]

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

[Anonymous]

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 🙂

[Anonymous]

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

[Anonymous · Accepted solution]

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 🙂

[Anonymous]

Thank you so much! this worked..