Matching Wrong Rows From Two Tables

Cyclist — Fri, 17 Jun 2016 14:39:26 GMT

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?

Re: Matching Wrong Rows From Two Tables

RonAgresta — Fri, 23 Sep 2016 12:14:11 GMT

Hi,

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.

Ron

topic Re: Matching Wrong Rows From Two Tables in SAS Data Management

Matching Wrong Rows From Two Tables

Re: Matching Wrong Rows From Two Tables