Converting from "link_only" to "dedupe_only"

[jbailiss]

I have a link match working well between multiple systems/datasets and I am now trying to covert that to identifying duplicates within those systems/datasets.

I have mostly re-used the same settings with a change to the link_type variable. I have hit a wall with converting this custom location comparison which was kindly shared by MOJ. I understand it well enough to know what it's doing and make small changes but not well enough to rewrite it for a dedupe where there aren't left and right datasets.

location_comparison = cl.CustomComparison(
    output_column_name="location",
    comparison_levels=[
        {
            "sql_condition": """
                (postcodes_l IS NULL OR array_length(postcodes_l) = 0)
                OR
                (postcodes_r IS NULL OR array_length(postcodes_r) = 0)
            """,
            "is_null_level": True,
        },
        cll.ArrayIntersectLevel("uprns", min_intersection=1),
        cll.ArrayIntersectLevel("postcodes", min_intersection=1),
        # Does at least one of the postcodes match on outcode?
        {
            "sql_condition": """
            array_length(list_intersect(
                list_transform( postcodes_l, pc -> substr( replace(pc, ' ', ''),1, length(replace(pc,' ','')) - 3) ),
                list_transform( postcodes_r, pc -> substr( replace(pc, ' ', ''),1, length(replace(pc,' ','')) - 3) )
            )) > 0
            """,
            "label_for_charts": "Exact match on first half of postcode",
        },
        cll.ElseLevel(),
    ],
    comparison_description="Comparison of location using UPRNs and Postcodes.",
)

Any help would be much appreciated! Similarly if there are any other tips for optimising existing "link_only" code to instead work with "dedupe_only". I can share the code in its entirety if that would be useful.

Edit: took the sample code out of 'code' brackets to preserve line breaks

[RobinL]

Is there a reason it needs to be changed? I believe the logic here is 'symmetrical' in the sense that it doesn't matter 'which way around the _l and _r records are.

TO give an example of a symmetrical and asymmetrical case.

Symetrical case: Consider the comparison of the names Robin and Rob. It doesn't matter whether you compare

• Robin to Rob
• Rob to Robin
  and hence the 'way around' the left and right record are is irrellevant

Assymetrical case: Consider a 'source' and 'destination' bank accounts for payments. The payment lands in the destination strictly after the source. Supposing the 'source' payment is dated 10th July and destination payment appears 14th July. The 'order matters'. That's a very different situation to the source payment leaving the account 14th July and a payment in the destination that arrives on 10th July.

When we move from link_only to dedupe_only, the only thing that's changing is that the _l and _r records now have no 'special status' in the sense that it's no longer the case that _l comes from one dataset and _r always comes from a second dataset.

I believe this location comparisons is 'symmetrical' and so it's just as relevant to the link_only case as it would be to the dedupe_only case