Topic
  • 2 replies
  • Latest Post - ‏2013-03-14T13:44:32Z by SystemAdmin
SystemAdmin
SystemAdmin
294 Posts

Pinned topic Removing Duplicate from the result of Data Rule?

‏2013-03-06T15:27:54Z |
I created a data rule that is a concatenation of three columns to compare it to the fourth column and see if the data is accurate
District + boro + num = dbn. The data rule seems to work except I keep getting duplicate dbn. How do I remove the duplicates please
if district exists AND len(trim(district)) 0 AND boro exists AND len(trim(boro)) 0 AND num exists AND len(trim(num)) THEN dbn matches_format '99A999'
Updated on 2013-03-14T13:44:32Z at 2013-03-14T13:44:32Z by SystemAdmin
  • smithha
    smithha
    162 Posts

    Re: Removing Duplicate from the result of Data Rule?

    ‏2013-03-11T13:51:43Z  
    I'm presuming that by 'duplicates' you are getting multiple records that either meet or fail your rule?
    And also that you just want to see the invalid combinations or possibly get a count of those, but don't care about the specific records involved?

    Assuming yes to both the above points, then removing duplicates depends on what version you are on.
    In 9.1, there is a specific Output option to only produce distinct values.
    In 8.5 and 8.7, you need to explicitly include an aggregation function in the output with a groupBy clause to trigger discrete records. For example, add a Custom Expression to your output such as: Count(recordID, dbn) where dbn is the grouping value. You may need to include the count in the rule definition as well.

    Harald
  • SystemAdmin
    SystemAdmin
    294 Posts

    Re: Removing Duplicate from the result of Data Rule?

    ‏2013-03-14T13:44:32Z  
    • smithha
    • ‏2013-03-11T13:51:43Z
    I'm presuming that by 'duplicates' you are getting multiple records that either meet or fail your rule?
    And also that you just want to see the invalid combinations or possibly get a count of those, but don't care about the specific records involved?

    Assuming yes to both the above points, then removing duplicates depends on what version you are on.
    In 9.1, there is a specific Output option to only produce distinct values.
    In 8.5 and 8.7, you need to explicitly include an aggregation function in the output with a groupBy clause to trigger discrete records. For example, add a Custom Expression to your output such as: Count(recordID, dbn) where dbn is the grouping value. You may need to include the count in the rule definition as well.

    Harald
    Thank you Harald