Editing the pattern indicator and using it in a column analysis - Cloud - 7.3

Talend Studio User Guide

Version
Cloud
7.3
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Cloud
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Design and Development
Last publication date
2024-02-13
Available in...

Big Data Platform

Cloud API Services Platform

Cloud Big Data Platform

Cloud Data Fabric

Cloud Data Management Platform

Data Fabric

Data Management Platform

Data Services Platform

MDM Platform

Real-Time Big Data Platform

Before being able to use regular expressions on analyzed columns in a database, you must first declare the created regular expression function by adding the SQL instruction and the pattern.

Before you begin

  • You have created a regular expression on Teradata.
  • You have selected the Profiling perspective.

Procedure

  1. In the DQ Repository tree view, expand Libraries > Indicators > System Indicators > Pattern Matching.
  2. Double-click Regular Expression Matching, or right-click it and select Open from the contextual menu.
    The corresponding view displays the indicator metadata and its definition.
  3. Scroll down to the Teradata line.
  4. Click Edit....
    The Edit expression dialog box opens.
  5. In the Expression box, add the following SQL expression:
    SELECT COUNT(CASE WHEN Regex_INSTR(<%=__COLUMN_NAMES__%>,<%=__PATTERN_EXPR__%>) =1 THEN 1 END), COUNT(*)FROM <%=__TABLE_NAME__%> <%=__WHERE_CLAUSE__%>
  6. Add a pattern indicator into a column analysis:
    1. In the Analysis Setting page, expand the Data preview part.
    2. Click Select indicators.
    3. Expand Patterns and select the patterns you need.
      The new expression will be used no matter what patterns you are selecting.
  7. Run the analysis and switch to the Analysis Results page.
  8. In the Pattern Matching table, right-click the pattern results and select View invalid rows, for example. The SQL editor opens listing invalid data and the SQL expression looks like the following:
    SELECT * FROM "test"."Employee" WHERE ( not REGEX_INSTR("FULLNAME" , '^[A-Z]+' ) =1 OR "FULLNAME" IS NULL )