Analytics

Name Standardization & Filtering

Articles · 2026 · Ioannis (John) Konstas

Reusable ACL Analytics patterns for text normalization, structured key generation, multi-segment name codes, and exclusion filters for audit-ready datasets.

Core ACL Functions

§1

Function	Syntax	Purpose
`UPPER()`	`UPPER(Name)`	Convert all characters to uppercase — required before any matching or comparison
`INCLUDE()`	`INCLUDE(str, charset)`	Keep only characters that appear in the character set — strips numbers, spaces, punctuation
`SUBSTR()`	`SUBSTR(str, start, length)`	Extract a fixed-length segment starting at position start (1-based)
`ALLTRIM()`	`ALLTRIM(Name)`	Remove all leading and trailing whitespace

Rule: Always apply UPPER() and INCLUDE() before SUBSTR(). Cleaning must happen before extraction — never extract from dirty data.

Standard Name Cleaning

§2

Single name field — 33-char clean key

SUBSTR(
  INCLUDE(UPPER(Name),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
  1, 33)

Full name field — same logic, explicit field name

SUBSTR(
  INCLUDE(UPPER(Full_Name),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
  1, 33)

Define as computed field for reuse

DEFINE FIELD Clean_Name COMPUTED
  SUBSTR(INCLUDE(UPPER(
    ALLTRIM(Name)),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
  1, 33)

Name Combination Formats

§3

Combining fields before cleaning is intentional — concatenation first, then INCLUDE strips any space between the two values, producing a compact joined key.

First + Last name key

SUBSTR(
  INCLUDE(UPPER(First_Name + Last_Name),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
  1, 33)

Last + First name key (surname-first matching)

SUBSTR(
  INCLUDE(UPPER(Last_Name + First_Name),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
  1, 33)

Initial-Based & Positional Formats

§4

These patterns extract specific character positions after cleaning — useful when target systems use fixed-position codes rather than full-name keys.

Last initial (2 chars) + full first name

SUBSTR(INCLUDE(UPPER(Last_Name),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+
SUBSTR(INCLUDE(UPPER(First_Name),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 33)

FL field — positions 1-2 and 5-6

SUBSTR(INCLUDE(UPPER(FL),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+
SUBSTR(INCLUDE(UPPER(FL),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 5, 2)

Structured ID Patterns

§5

Last (4) + First (3) — compact 7-char key

SUBSTR(INCLUDE(UPPER(Last_Name),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 4)
+
SUBSTR(INCLUDE(UPPER(First_Name),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 3)

Multi-segment — First[1-2]+First[5-6]+Last[1-2]+Last[4-5]

SUBSTR(INCLUDE(UPPER(First_Name),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+SUBSTR(INCLUDE(UPPER(First_Name),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 5, 2)
+SUBSTR(INCLUDE(UPPER(Last_Name), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+SUBSTR(INCLUDE(UPPER(Last_Name), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 4, 2)

Name + Title code — 8-char compound key

SUBSTR(INCLUDE(UPPER(First_Name),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+SUBSTR(INCLUDE(UPPER(Last_Name), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+SUBSTR(INCLUDE(UPPER(Title),      "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 2)
+SUBSTR(INCLUDE(UPPER(Title),      "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 8, 2)

Record Exclusion Filter

§6

Use in an ACL WHERE clause or EXTRACT IF condition to exclude known non-target descriptions before analysis. Place exclusions at the top of your script to reduce dataset size early.

Exclusion filter — WHERE / EXTRACT IF clause

EXTRACT RECORD IF
  Description <> "Name Siemens"
  AND Description <> "Metro Police Department"
  AND Description <> "Metro Fire Rescue"
  AND Description <> "Facility Contractors"
  AND Description <> "Resort Spa"
  AND Description <> "Resort Cafe"
  AND Description <> "Uniform Cleaners"
  AND Description <> "Contractor Visitor Vendor"
  AND Description <> "Noodle Bar"
  AND Description <> "Food Court Vendors"
  AND Description <> "Emergency Management"
  TO "Filtered_Records"

For longer exclusion lists, maintain a secondary table of excluded descriptions and use a JOIN UNMATCH instead of hard-coded <> conditions. This makes the list maintainable without script edits.

Best Practices & Common Use Cases

§7

Rules

Always run INCLUDE() before SUBSTR() — extract from clean data only
Always apply UPPER() before any text matching, join, or duplicate check
Use fixed-length SUBSTR() widths — variable keys cause join failures
Define cleaned fields with DEFINE FIELD … COMPUTED so the logic runs once and is reused
Apply exclusion filters early — reduce the working dataset before any heavy operations
Test key uniqueness with DUPLICATES ON key_field before using keys in joins

Common Use Cases

Duplicate employee or vendor detection across datasets with inconsistent name formatting
Employee/vendor ID generation for cross-system matching (HR vs. payroll vs. ERP)
Normalizing import data before joining to master tables
Cleaning access logs or transaction descriptions before frequency analysis
Fuzzy-match preparation — generate multiple key variants (First+Last, Last+First) and join on all of them

← All articles

Research and direction by Ioannis (John) Konstas; compiled and finalized with AI assistance.