Delete Specific Rows

[mustufa2019]

I am trying to delete specific rows within a column. My data file keeps repeating the below highlighted in yellow rows and I was wondering if I can create a filter or function that will delete these rows. 

[nerces · Accepted solution]

There are various ways to do this, one way that I would suggest is to use the filter tool in combination with the LEFT function. Your custom expression in the filter tool should look something like:

LEFT([FieldName],19) = "ACS SECURITY REPORT" OR LEFT([FieldName],8) = "POST ACS" OR LEFT([FieldName],11) = "ID INITIALS"

As I said, there are multiple ways of doing this, so it may not be the best method for you, but it should get on you track to a solution that meets your needs.

[jrgo]

Hi @mustufa2019 

Without a sample to work with, not sure if this would work directly with your actual data, but REGEX is how I'd approach this. Using a REGEX_MATCH() function in a filter would split the records that matched the specified pattern.

Here is the REGEXpression i entered into the Filter. You'll need to be sure you update the "[Field1]" to whatever your field name is.

REGEX_MATCH([Field1], '^[A-Z]+\s+[A-Z]+\s+.+\s+[A-Z]{2}\d+\s+[A-Z]+\s+.+$')

Jimmy
Teknion Data Solutions

[mustufa2019]

@nerces Thank you so much, this worked! I have question in regards to the scripts:

LEFT([SJOB: JG541ACS RUN DATE: 04/27/19 TIME: 1:58:31 BANK: 541 ID: 541 ],19) = "ACS SECURITY REPORT" OR

What does the "19" in the above script refer to?

[mustufa2019]

@jrgo thank you so much! this worked too but i'm trying to understand the logic of this script

[jrgo]

Here's the break down of each token to match a string pattern for...

^ = from the beginning of the string/line

[A-Z]+ = any letter 1 or more times

\s+ = a space 1 or more times

[A-Z]+ = any letter 1 or more times

\s+ = a space 1 or more times

.+ = any character 1 or more times

\s+ = a space 1 or more times

[A-Z]{2} = two consecutive letters

\d+ = any number 1 or more times

\s+ = a space 1 or more times

[A-Z]+ = any letter 1 or more times

\s+ = a space 1 or more times

.+ = any character 1 or more times

$ = to the end of string/line

[markcurry]

19 is the number of characters it reads from the left (start of the string).  It’s 19 because that’s the length of "ACS SECURITY REPORT"

[mustufa2019]

@markcurry that makes sense! However, i noticed the "Post ACS" field is still not being removed. Is there something i missed in the function?

[markcurry]

It’s hard to see from the screenshot, but are there 2 spaces between Post and ACS? If so, add the extra space to your left function and change it to 9 characters.

[mustufa2019]

No, there are not. i tried few combination 7,8,9 and etc. Still doesnt work... @markcurry