Function for duplicate SSN's

Carolyn Rush
Carolyn Rush Member
edited December 2023 in General

Hello,

My first post in the community so please bear with me.

Trying to create a function to catch duplicate SSN's. So far, my preview is not working once all of the J Segments are added in.

The function that needs help is:

Create list: Base + all J segments>is empty>is true

Equals (invert output) BaseSSN=input value J1_1SSN - J2_5SSN=comparison values

Equals (invert output) J1_1 SSN=input value J1_2SSN - j2_5SSN=comparison values etc etc

Each Equals is connected to an "or'>is true>or (connected to is true above)

Has anyone created one of these functions before?

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited December 2022

    Hi Carolyn

    If I understand correctly you are trying to find values (SSN) that are duplicated in different columns (Base/J Segment) in the same record/row?

    image.png

    I think the issue you are having is Inverting the output of each Equals instead you should use a Not to invert the result of the function:

    image.png


  • Carolyn Rush
    Carolyn Rush Member

    Hello Josh,

    Thank you for taking the time to look over this with me. I have tried to replicate the above functions and not having any success. Would it be ok if I sent you the script to look over via email because of how large it is?

  • Josh Boxer
    Josh Boxer Administrator

    Hi Carolyn

    I updated my answer above keeping it close to the approach you were already building. Hopefully easy to understand but feel free to share more detail if you are not getting what you expected.

    Another approach that might be useful, especially if there are many columns to compare is to use the List transformation functions.

    Create list containing all the SSN columns, using 'List frequency', see that a count is added after each value, so 987 appears 3 times in the list.

    image.png

    In this case you don't want the frequency of every value only the most common:

    image.png

    If the frequency of the most common value is greater than 1 then return true (or Invert output to return false for the function)

  • Carolyn Rush
    Carolyn Rush Member

    Good morning Josh,

    Once again, thank you for your continued help with this issue! We believe the reason we cannot get this function to work is because some of the columns could be empty and thus results in a false negative. We have tried adding the 'is empty' validation, but no success.

  • Josh Boxer
    Josh Boxer Administrator

    Hello, yes you can use is empty to check value is not null before comparing against other columns:

    image.png

    Or if using the List option, remove any blanks that have been added to the list:

    image.png


Categories