Filter Check if Value Contains Only Dates - Highlight Errors

Hey - I am looking to validate some data, the aim is to highlight only where the data does not contain a date (anything different from format YYYY/MM/DD HR:MM). Here is my table - I would want it to highlight everything in the “Open Date” Colums that is not a date - so below “47T”
Test.xlsx (13.6 KB)

Hi @DavidO

You probably meant to upload the dataset you used in your other topic :wink: That one contains a 47T record, here you only have proper dates.

Also here a lot of ways to go, one is via a RegexMatcher that searches for that particular date pattern. If this varies, then the code needs to be amended accordingly off course.

regexMatcher(column("column1"),"[0-9]{1,2}[\\/][0-9]{1,2}[\\/][0-9]{2}.*")

Again a Row Filter node should get the desired records.

Hope this helps!

1 Like

@ArjenEX yes, looks like I included the wrong file. Knime formats the date a little different from my other file so here is the file formatted correctly. Also curious, is a regexMatcher just a format matcher for each individual value?
Test.xlsx (12.1 KB)

Yes, the Regexmatcher evaluates each row individually. For this example: same approach, slightly different regex.

if (column("Open Date") != null) {
    regexMatcher(column("Open Date"),"[0-9-]{10}[A-Z][0-9:]+")
} else {
    null
}

The null check is in place because a regexmatch on a null value throws an error.

1 Like