Skip to content
CA Test Data Manager - 4.1
Documentation powered by DocOps

Filter Options for Transformation Maps

Last update August 2, 2017

The following list includes all the available filter options and transformation map conditions in the Datamaker UI. 

Filter Name Description Condition
ALL Identifies all the columns.  
CHECKED Identifies all the checked columns. (checked = 'Y')
VALIDATED Identifies all the validated columns. (validated = 'Y')
APPROVED Identifies all the approved columns. (approved = 'Y')
KEY Identifies all the columns containing primary key records. (tpd_pkey_count > 0) or (tpd_ukey_count > 0)
INDEXED Identifies all the columns containing indexed records. tid_index_count > 0
EMPTY Identifies all the columns that are defined as empty. (tcs_nullcount > 0) and (tcs_rowcount = 0)
UNIQUE Identifies all the columns that contain unique values. (tcs_distinct_count = tcs_rowcount) and (tcs_rowcount > 0)
GUID Identifies all the columns that contain a GUID. (upper (tcs_sample_analysis) like '%GUID%')
SEQUENTIAL Identifies all the columns that contain a sequential numeric list. (upper (tcs_sample_analysis) like '%SEQUENCE%')
DATE Identifies all the columns that contain a date. case (upper (left (tcd_datatype, 9))
when 'DATE","DATETIME","TIMESTAMP"
then 1 else 0) = 1
or case (pos (tcd_format, "YY")
when is > 0 then 1 else 0) = 1
or (pos (lower (tcd_column_name), "_dat") > 0
and pos (lower (tcd_column_name), "_dat")
<> pos (lower (tcd_column_name), "_data"))
DOB Identifies all the columns that contain a DOB.

((upper (tcs_sample_analysis) like '%DOB%')
or (upper (tcd_column_name) like '%DAT%BIRTH%')
or (upper (tcd_column_name) like '%DOB%'))

TAILING DATES

Identifies all the columns that contain values with tailing dates.

(upper (tcs_sample_analysis) like '%TAILING_DATES%')
NAME Identifies all the columns that contain a name. ((upper (tcd_column_name) like '%NAM%')
or (upper (tcd_column_name) like '%NM%'))
+ is_char_only
ADDRESS Identifies all the columns that contain an address. ((upper (tcd_column_name) like '%ADD%')
or (upper (tcd_column_name) like '%ADR%')
or (upper (tcd_column_name) like '%CITY%')
or (upper (tcd_column_name) like '%STREET%')
or (upper (tcd_column_name) like '%STATE%')
or (upper (tcd_column_name) like '%POST%CODE%')
or (upper (tcd_column_name) like '%COUNT%Y')
OR (upper (tcs_sample_analysis) like '%ADDRESS%')
OR (upper (tcs_sample_analysis) like '%USZIP%')
or (upper (tcs_sample_analysis) like '%UKPOSTCODE%')
or (upper (tcs_sample_analysis) like '%POSTAL%'))
+ is_char_only
SSN Identifies all the columns that contain US Social Security numbers. ((upper (tcd_column_name) like '%SS%')
and (upper (tcd_column_name) not like '%ESS%'))
or (upper (tcd_column_name) like '%SOCIAL%')
OR (upper (tcs_sample_analysis) like '%US-SSN%')
EMAIL Identifies all the columns that contain email values. ((upper (td_table_name) like '%EMAIL%')
or (upper (tcd_column_name) like '%EMAIL')
or (upper (td_table_name) like 'EMAIL%')
OR (upper (tcs_sample_analysis) like '%EMAIL%'))
UK NINO Identifies all the columns that contain UK NI numbers. ((upper (tcs_sample_analysis) like '%UK-NINO')
or (upper (tcd_column_name) like '%NAT%INS%')
or (upper (tcd_column_name) like '%NI%NO%'))
+ is_char_only
CREDIT CARD Identifies all the columns that contain credit card numbers. (upper (tcs_sample_analysis) like '%CREDITCARD%')
IBAN Identifies all the columns that contain IBAN numbers. (upper (tcs_sample_analysis) like '%IBAN%')
FINANCIAL DATA Identifies all the columns that contain financial data. (upper (tcd_column_name) like '%ACCOUNT%')
or (upper (tcd_column_name) like '%SALARY%')
or (upper (tcd_column_name) like '%REVENUE%')
or (upper (tcd_column_name) like '%PROFIT%')
or (upper (tcd_column_name) like '%SALES%')
or (upper (tcd_column_name) like '%TRANSACTION%')
PHONE NUMBER Identifies all the columns that contain phone numbers. (upper (tcd_column_name) like '%PHONE%')
or (upper(tcd_column_name) like '%NUMBER%')
or (upper (tcs_sample_analysis) like '%PHONE%')
HIGH DISTINCT COUNT (CHARS) Identifies all the columns with high distinct character data types. (upper (tcs_sample_analysis) like '%HIGHDISTINCT_C%')
HIGH DISTINCT COUNT (NUMERIC) Identifies all the columns with high distinct numeric data types. (upper (tcs_sample_analysis) like '%HIGHDISTINCT_N%')
MIX OF ALPHANUMERIC Identifies all the columns that contain alphanumeric data. (upper (tcs_sample_analysis) like '%ALPHANUMERIC%')
MIXED CASE Identifies all the columns that contain values in mixed case. (upper (tcs_sample_analysis) like '%MIXEDCASE%')
CONTAINS SPACES Identifies all the columns that contain spaces. (upper (tcs_sample_analysis) like '%SPACES%')
CONTAINS SPECIAL CHARACTERS Identifies all the columns that contain special characters. (upper (tcs_sample_analysis) like '%SPECIALCHARS%')
NUMERIC CHARACTER Identifies all the columns that contain numeric characters. (upper (tcs_sample_analysis) like '%C-NUMERIC%')
FORMATTED NUMERIC DATA Identifies all the columns that contain formatted numeric data. (upper (tcs_sample_analysis) like '%NUM-PATTERN%')
TEXT Identifies all the columns that contain text. ((upper (tcd_datatype) like '%CHAR%') and (tcd_precision > 254))
or (upper (tcd_datatype) like '%STRING%')
or (upper (tcd_datatype) like '%TEXT%')
or (upper (tcd_datatype) like '%CLOB%')
VALUES FROM SEED LIST Identifies all the columns that contain values from seed lists. <seedlist>
TRANSFORMED Identifies all the columns that contain transformed values. <transformed>
CUSTOM Allows you to create a customized filter. See Custom Filter Functions for Transformation Maps.

In this table, is_char_only is short for the following expression:
and (NOT (case (upper (left (tcd_datatype, 9)) when "DATE","DATETIME","TIMESTAMP","NUMBER","NUMERIC","INTEGER" then 1 else 0) = 1 or case (pos (tcd_format, "YY") when is > 0 then 1 else 0) = 1))

Relevant Columns

   
checked  case when tfc_auth_stage >= 1 then 'Y' else 'N' end
validated case when tfc_auth_stage >= 2 then 'Y' else 'N' end

approved

case when tfc_auth_stage >= 3 then 'Y' else 'N' end
td_table_name coalesce (TFC_TABLE_NAME, TD_TABLE_NAME)
tcd_column_name coalesce (TFC_COLUMN_NAME, TCD_COLUMN_NAME)
tcd_column_seq  
tcd_datatype  
tcd_precision   
tcd_scale   
tcd_nullable CASE when tcd_nullable = '0' then 'N' else 'Y' end
TFC_ACTION In gtrep_transformation_column table
TFC_DEFAULT In gtrep_transformation_column table
TFC_FUNCTION In gtrep_transformation_column table
tree_icon case when tfd_fkey_count + tfd_rel_count > 0 then 'treeview16.gif' else null end
tfd_rel_count

(select count (rel_name)
From gtrep_relationship
join gtrep_rel_column on rc_rel_id = rel_id
where rc_parent_column = tcd_column_name
and rel_parent_table = td2.td_table_name
and rel_proj_id in (select pv_proj_id from gtrep_project_version where pv_id = <projectVersion>))

tfd_fkey_count (select count (tfd_fkey_name)
From gtrep_table_fkey_def
where tfd_ref_column_name = tcd_column_name
and tfd_ref_table_name = td2.td_table_name
and tfd_pv_id == <projectVersion>)

TFC_XREF

In gtrep_transformation_column table
TFC_XREF_IDENT In gtrep_transformation_column table
TFC_KEEPNULLS In gtrep_transformation_column table
TFC_LIST_COLNO In gtrep_transformation_column table
TFC_OVERRIDE_LOOKUP In gtrep_transformation_column table
TFC_UNIQUE_COLS In gtrep_transformation_column table
TFC_NOTES In gtrep_transformation_column table
TFC_PREFORMAT In gtrep_transformation_column table
TCD_FORMAT In gtrep_transformation_column table 
where_clause_yes case when coalesce (tfc_where_id, 0) > 0 then 'Y' else '' end
TFC_WHERE_SEQ In gtrep_transformation_column table
tfc_where_clause pk_gtrep_cl.f_get_data (TFC_WHERE_ID)
tcs_sample_analysis

(SELECT max (tcs_sample_value)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'analysis'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'analysis'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))

tcs_sample

'Sample'

TPD_PKEY_COUNT PK_CNT
PK_CNT count (PKEY_NAME) (see reference 2)
TPD_UKEY_COUNT UK_CNT
UK_CNT count (ukey_name) (see reference 2)
tid_index_count IDX_CNT
IDX_CNT count (TID_INDEX_NAME) (see reference 2)
auth_stage tfc_auth_stage
id coalesce (tfc_id, 0)
tfc_xpath_element In gtrep_transformation_column table
tfc_dateformat In gtrep_transformation_column table
tfc_column_part_s In gtrep_transformation_column table
tfc_column_part_l In gtrep_transformation_column table

tcs_rowcount

(SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'rowcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'rowcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))
tcs_distinct_count (SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'distcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'distcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))
tcs_nullcount (SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'nullcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'nullcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id ))
tfc_order In gtrep_transformation_column table

Reference 1 - gtrep_transfomation_column

  • tfc_transformation_map
  • tfc_proj_id
  • tfc_table_name
  • tfc_pv_id
  • tfc_column_name
  • tfc_where_seq
  • tfc_action
  • tfc_default
  • tfc_function
  • tfc_list_colno
  • tfc_keep_nulls
  • tfc_xref
  • tfc_xref_ident
  • tfc_unique_cols
  • tfc_notes
  • tfc_override_lookup
  • date_created
  • who_created
  • program_created
  • date_updated
  • who_updated
  • program_updated
  • tfc_auth_stage
  • tfc_id
  • tfd_dateformat
  • tfc_xpath_element
  • tfc_column_part_l
  • tfc_column_part_s
  • tfc_preformat
  • tfc_where_id
  • tfc_order

Reference 2

select TD_TABLE_ID,
  count (TID_INDEX_NAME) IDX_CNT,
  count (PKEY_NAME) PK_CNT,
  count (ukey_name) UK_CNT
from gtrep_table_def
left outer join gtrep_table_ind_def on tid_table_id = td_table_id
left outer join (
    select tpd_table_id, tpd_pukey_name pkey_name
    from gtrep_table_pukey_def
    where tpd_pv_id = <projectVersion>
    and tpd_pukey_type = 'P'
) pk on pk.tpd_table_id = td_table_id
left outer join (
    select tpd_table_id, tpd_pukey_name ukey_name
    from gtrep_table_pukey_def
    where tpd_pv_id = <projectVersion>
    and tpd_pukey_type = 'U'
) uk on uk.tpd_table_id = td_table_id
where (td_table_id in (<list of tableIds>) or (coalesce(:ps_all,'N')='ALL'))
and td_pv_id = <projectVersion>
group by TD_TABLE_ID

Reference 3 - Main Query 

SELECT
    'columns16.gif' col_icon,
    'add16.gif' new_icon,
    'delete16.gif' delete_icon,
    case when tfc_auth_stage >= 1 then 'Y' else 'N' end as checked,
    case when tfc_auth_stage >= 2 then 'Y' else 'N' end as validated,
    case when tfc_auth_stage >= 3 then 'Y' else 'N' end as approved,
    coalesce (TFC_TABLE_NAME, TD_TABLE_NAME) as td_table_name,
    coalesce (TFC_COLUMN_NAME, TCD_COLUMN_NAME) as tcd_column_name,
    tcd_column_seq,
    tcd_datatype,
    tcd_precision,
    tcd_scale,
    tcd_nullable,
    TFC_ACTION,
    TFC_DEFAULT,
    TFC_FUNCTION,
    case when tfd_fkey_count + tfd_rel_count > 0 then 'treeview16.gif' else null end tree_icon,
    TFC_XREF,
    TFC_XREF_IDENT,
    TFC_KEEPNULLS,
    TFC_LIST_COLNO,
    TFC_OVERRIDE_LOOKUP,
    TFC_UNIQUE_COLS,
    TFC_NOTES,
    TFC_PREFORMAT,
    TCD_FORMAT,
    case when coalesce (tfc_where_id, 0) > 0 then 'Y' else '' end where_clause_yes,
    TFC_WHERE_SEQ,
    pk_gtrep_cl.f_get_data (TFC_WHERE_ID) tfc_where_clause,
    tcs_sample_analysis,
    'Sample' as tcs_sample,
    PK_CNT as TPD_PKEY_COUNT,
    UK_CNT as TPD_UKEY_COUNT,
    IDX_CNT as tid_index_count,
    tfc_auth_stage auth_stage,
    coalesce (tfc_id, 0) id,
    tfc_xpath_element,
    tfc_dateformat,
    tfc_column_part_s,
    tfc_column_part_l,
    tcs_rowcount,
    tcs_distinct_count,
    tcs_nullcount,
    tfc_order
FROM ( select td2.TD_TABLE_ID,
        td2.TD_PROJ_ID,

        td2.TD_TABLE_NAME,
        td2.TD_PV_ID,
        TCD_COLUMN_NAME,
        TCD_COLUMN_SEQ,
        TCD_DATATYPE,
        TCD_PRECISION,
        TCD_SCALE,
        TCD_FORMAT,
        CASE when tcd_nullable = '0' then 'N' else 'Y' end as tcd_nullable,
        IDX_CNT,
        PK_CNT,
        UK_CNT,
        (SELECT max (tcs_sample_value)
            FROM gtrep_tc_sample
            WHERE gtrep_tc_sample.tcs_sample_type = 'analysis'
            AND gtrep_tc_sample.tcs_column_name = tcd_column_name
            AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
            AND gtrep_tc_sample.tcs_sample_date in (
                SELECT max(tcs_sample_date)
                FROM gtrep_tc_sample
                WHERE tcs_sample_type = 'analysis'
                AND tcs_column_name = tcd_column_name
                AND tcs_table_id = td2.td_table_id
            )
        ) as tcs_sample_analysis,
        (SELECT cast (max (tcs_sample_value) as numeric)
            FROM gtrep_tc_sample
            WHERE gtrep_tc_sample.tcs_sample_type = 'rowcount'
            AND gtrep_tc_sample.tcs_column_name = tcd_column_name
            AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
            AND gtrep_tc_sample.tcs_sample_date in (
                SELECT max(tcs_sample_date)
                FROM gtrep_tc_sample
                WHERE tcs_sample_type = 'rowcount'
                AND tcs_column_name = tcd_column_name
                AND tcs_table_id = td2.td_table_id
            )
        ) as tcs_rowcount,
        (SELECT cast (max (tcs_sample_value) as numeric)
            FROM gtrep_tc_sample
            WHERE gtrep_tc_sample.tcs_sample_type = 'distcount'
            AND gtrep_tc_sample.tcs_column_name = tcd_column_name
            AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
            AND gtrep_tc_sample.tcs_sample_date in (
                SELECT max(tcs_sample_date)
                FROM gtrep_tc_sample
                WHERE tcs_sample_type = 'distcount'
                AND tcs_column_name = tcd_column_name
                AND tcs_table_id = td2.td_table_id
            )
        ) as tcs_distinct_count,
        (SELECT cast (max (tcs_sample_value) as numeric)
            FROM gtrep_tc_sample
            WHERE gtrep_tc_sample.tcs_sample_type = 'nullcount'
            AND gtrep_tc_sample.tcs_column_name = tcd_column_name
            AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
            AND gtrep_tc_sample.tcs_sample_date in (
                SELECT max(tcs_sample_date)
                FROM gtrep_tc_sample
                WHERE tcs_sample_type = 'nullcount'
                AND tcs_column_name = tcd_column_name
                AND tcs_table_id = td2.td_table_id
            )
        ) as tcs_nullcount,
        (select count (rel_name)
            from gtrep_relationship
            join gtrep_rel_column on rc_rel_id = rel_id
            where rc_parent_column = tcd_column_name
            and rel_parent_table = td2.td_table_name
            and rel_proj_id in (select pv_proj_id from gtrep_project_version where pv_id = :pl_pv_id)
        ) as tfd_rel_count,
        (select count (tfd_fkey_name)
            from gtrep_table_fkey_def
            where tfd_ref_column_name = tcd_column_name
            and tfd_ref_table_name = td2.td_table_name
            and tfd_pv_id = :pl_pv_id
        ) as tfd_fkey_count

    from ( select TD_TABLE_ID,
            count (TID_INDEX_NAME) IDX_CNT,
            count (PKEY_NAME) PK_CNT,
            count (ukey_name) UK_CNT
        from gtrep_table_def
        left outer join gtrep_table_ind_def on tid_table_id = td_table_id
        left outer join ( select tpd_table_id,
            tpd_pukey_name pkey_name
            from gtrep_table_pukey_def
            where tpd_pv_id = :pl_pv_id
            and tpd_pukey_type = 'P') pk on pk.tpd_table_id = td_table_id
        left outer join (select tpd_table_id,
            tpd_pukey_name ukey_name
            from gtrep_table_pukey_def
            where tpd_pv_id = :pl_pv_id
            and tpd_pukey_type = 'U') uk on uk.tpd_table_id = td_table_id
        where (td_table_id in (:pl_table_id) or (coalesce(:ps_all,'N')='ALL'))
        and td_pv_id = :pl_pv_id
        group by TD_TABLE_ID
    ) td1
    join gtrep_table_def td2 on td2.td_table_id = td1.td_table_id
    join gtrep_table_col_def on tcd_table_id = td1.td_table_id
) td
left outer join GTREP_TRANSFORMATION_COLUMN
ON TFC_TRANSFORMATION_MAP = :ps_transformation_map
AND TFC_TABLE_NAME = TD_TABLE_NAME
AND TFC_COLUMN_NAME = TCD_COLUMN_NAME
AND TFC_PV_ID = TD_PV_ID

Arguments are <table list ids> <projectVersion> ps_transformation_map ps_all.

Sorting was done on td_table_name A tcd_column_seq A has_where_clause A tfc_where_seq A.   

Was this helpful?

Please log in to post comments.