tRuleSurvivorship : implement a rule like "latest non null"

[vdubois]

Hello,

I can't manage to implement a rule in tRuleSurvivorship.

I have 3 records:

GID     Name      LastModification

1          null          2018-05-20

1          Titi           2018-05-18

1          Tata         2018-05-15

I would like to get Titi as Name in the golden record because this is the most recent record which has the column Name not null.

I have tried to parameter a rule with

• Column reference : LastModification
• Fonction: Most recent
• Target colum: Name

So Talend keeps the most recent record and my golden record doesn't have any name => the last record doesn't have any name

I would like a combination of "most recent" for the colum LastModification and "non null" for Name.

How can I do this ?

Thanks for your help

[Anonymous]

Hi VDubois,

Have you tried to filter first the record that doesnt contain any name then applied your tRuleSurvivorship?

Alternatively, you can do it straight into the tRuleSurvivorship as mentioned in the documentation:

https://help.talend.com/reader/g8zdjVE7fWNUh3u4ztO6Dw/HFvKvgRxBwuX339KgNfOmA

With an expression that should look like that ".length > 0" (sequential on the data and multi-condition on the length).

Cheers,

[vdubois]

Thanks for your reply.

I agree I could filter but I want to do it straight into the tRuleSurvivorship. It is more convenient for me to pull all the rules in the same place.

I have tried with ".length>0" but It doesn't work. In fact, it is an "AND" rule by using "multi condition". It will keep the record which satisfies the two conditions together, most recent and not null.

In my example, the first record is the most recent but its name is null. The second record has a name but is not the most recent. No record matches these conditions.So it returns NULL.

Or maybe I did something wrong ?

[Anonymous]

If you are using tMatchGroup before to get the group size and GID, you could in the previous component remove the null name (create a separate group) ?

Otherwise, I will be back to tFilter first which should be pretty quick (if the data come from a DBMS you could do the filtering in the SQL to increase the performance).

[vdubois]

Yes it will work with my example but in reality I have 50 columns, I have reduced my example to be more clear...

So I can't filter before because I will lose some datas that are required for other rules.

Moreover, I want to apply this kind of rule for 5 columns, I don't want to do it in 5 separate steps with 5 different filters...

It is really weird we can't do it in tRuleSurvivorShip using expression for example...