06-17-2016 10:39 AM
I am working with 2 tables: TBL_source and TBL_AdressMaster.
after I join these 2 tables by using matching codes, and cluster them, I want to select the surviving record.
For that purpose I define this expression in the surviving record element:
if Not isnull(`S_Door`) and compare(`AM_DOOR_NUMBER_CASED_MATCHCODE`, 'S_DOOR_CASED_MATCHCODE', true) == 0 then return true else if isnull(`S_Door`) and compare(`AM_DOOR_NUMBER_CASED`, 'UNKNOWN', true) == 0 then return true else return false
the idea is: when the door_numer is NULL and when there is match between source and adress master, in regard to door_number, then it should return 0.
When the door_numer is unknown, than it should mark exactly one row in the adress master which coinains the value "unknown" in the door_number field (this is to avoid large clusters).
Otherwise, return false.
The second part of the expression (door_number IS NULL) seems to work as expected.
The first part returns unexpeted results: although there is a record in the adress_master with exactly the same door_number as in the source table, the software matches BUT DOES NOT MARK as the surviving record the correct row from the adressmaster.
Can anyone explain why? Did I define the expression correctly? Or do I need to do something else?
09-23-2016 08:14 AM
I don't know if you have gone this route, but have you reviewed the code generated by rules defined by the wizard interface and then used that as your guide for the custom expression code? If it's all wizard-defined rules, there is a unique interplay between record level rules, field level rules, and various options to chain results of one rule to the output of another. It could be you don't quite have those options set correctly.
Check out Data Management Studio documentation (search for "surviving" on the lefthand search panel) for more details on record survivorship.