SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Matching Wrong Rows From Two Tables

Reply
Contributor
Posts: 23

Matching Wrong Rows From Two Tables

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?

SAS Super FREQ
Posts: 90

Re: Matching Wrong Rows From Two Tables

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

Ask a Question
Discussion stats
  • 1 reply
  • 431 views
  • 0 likes
  • 2 in conversation