How to look for ID's in the same column without using Group and Latest date with filtering

Jacky Lu
Jacky Lu Experian Employee
in General

Hi @Danny Roden ,

I have 2 scenarios i wanted to run past you to get some advise, please:

  1. I have a list of ID's in a column and I want to be able to find out using a validate step (Function canvas) to see which ID's have come up in that column twice without having to use the group step

2. I have a date column with different dates. Same as scenario 1 - I want to be able to do this in the validate step without having to use a filter step to filter out the latest date and then compare it to todays date for a match.

Many Thanks

Answers

  • Danny Roden
    Danny Roden Administrator

    Thanks for the post @Jacky Lu , I'm assuming scenario 1 is like an 'isUnique' check right? If so, the best way to do this is to create a new column before the validate step with the count in. You can do this in a couple of ways, but I'd suggest using the lookup step.

    For example, here I've got a customerID column I want to check is unique:

    image.png

    First I'll feed my data into a lookup and perform a self lookup on the value in that column and select the 'count' lookup type:

    image.png
    image.png

    This adds a new column to my data with the count in:

    image.png

    Now my data can be fed into a validate step so as a simple rule of 'CustomerID_count must be =1' can be set:

    image.png
    image.png

    I'll respond to scenario 2 in a separate comment

  • Danny Roden
    Danny Roden Administrator

    For scenario 2, assuming my understanding of the task is correct, you could do this entirely within the Validate step using a single function:

    See here for an example I've mocked up where I'm taking an input, comparing it with today's date and then checking whether the number of days difference is 0 or not (this can easily be then adapted to perform more flexible checks, e.g. date is within the past week etc):

    image.png

Categories