Test Data Manager

Expand all | Collapse all

Function Masking with condition IF

Jump to Best Answer
  • 1.  Function Masking with condition IF

    Posted 07-06-2017 05:29 AM

    Hi,


    We need to mask fields from conditions specified by us.

    For example, the phone will be masked according to the following conditions. And we would need it to be reusable for all our projects regardless of the connection.

     

    IF ((SUBSTR(TELEPHONE,1,1) ='6') OR (SUBSTR(TELEPHONE, 1,1) ='7')) THEN

          TELEPHONE='666555666';

    ELSE

           IF ((SUBSTR(TELEPHONE,1,1) ='+') AND ((SUBSTR(TELEPHONE, 4,1) ='6') OR (SUBSTR(TELEPHONE, 4,1) ='7'))) THEN

                 TELEPHONE='+34666555666';

           ELSE

                  IF (SUBSTR(TELEPHONE,1,1) ='+') THEN

                       TELEPHONE='+34937280000';

                 ELSE

                        TELEPHONE='937280000';

                 END IF;

           END IF;

     END IF;

     

    How can we do it with Fast Data Masker?

     

    Thanks and regards.



  • 2.  Re: Function Masking with condition IF
    Best Answer

    Broadcom Employee
    Posted 07-06-2017 10:37 AM

    Hi Nieves,

    So the first thing I thought of was the fact we do not have if statements available in our Fast Data Masker product.  What do we have?  Reading the documentation at:

     

    Fast Data Masker Best Practices -> Add WHERE Clause to a Table

    https://docops.ca.com/ca-test-data-manager/4-1/en/provisioning-test-data/mask-production-data/fast-data-masker-best-practices#FastDataMaskerBestPractices-AddWHEREClausetoaTable

     

    Didn't get me where I wanted to be.  But, it provided a place to start.  I looked further in the documentation to:

     

    Define Masking Rules -> Select a Column to Mask

    https://docops.ca.com/ca-test-data-manager/4-1/en/provisioning-test-data/mask-production-data/mask-stored-data/mask-data-stored-in-relational-databases/define-masking-rules

     

    Select a Column to Mask

    After you select a table to mask, select a column to mask for the table.

    1. Select the table that you moved to the Masked Tables field.
      A tab with the same name as the table name opens in the right pane.
    2. Select a column that you want to mask from the Add column to mask drop-down list. 
      Note: Use the Where Condition field to set a SQL condition. If you use this field, only rows for the provided condition are masked. Fast Data Masker currently supports the following operators: <,<=,=, =>, > and LIKE (LIKE is not supported for flat files. It is only used for database masking).
    3. Click Add.
      Note: To select multiple columns to mask, click the Add Multiple Column icon, select multiple columns, and click OK.
      The column that you selected is displayed as a tab on the top right. The column that you are defining is highlighted in blue.
    4. (Optional) To remove a column, right-click the tab for that column and select Remove Column from the context menu.

    You have successfully selected a column that you want to mask for the selected table.

    Note: For regular flat-file masking, the same column cannot be masked twice in a given masking map. You can now select the mask type.

    __________________________________________________________

    This is something we could work with.  Relooking at your if statements, could they be reconfigured to be where clauses that would accomplish the same task?  My initial answer was yes.  I asked Development to confirm, and this is their response:

     

    Correct, where clauses is the only way I know to achieve it, unless there is a SQL function on the database to do the same

    Multiple where clauses on the table - so something like

     

    WHERE SUBSTR(TELEPHONE,1,1) ='6' OR SUBSTR( TELEPHONE, 1,1) ='7'

    TABLENAME,FIXED, '666555666'

    .

    .

    And the last where clause

    WHERE SUBSTR( TELEPHONE,1,1) <> '6' AND SUBSTR( TELEPHONE, 1,1) <> '7' AND SUBSTR( TELEPHONE, 1,1) <> '+'

    and not (SUBSTR(TELEPHONE,1,1) ='+') AND ((SUBSTR(TELEPHONE, 4,1) ='6') OR (SUBSTR(TELEPHONE, 4,1) ='7'))

    ___________________________________________________________

     

    Please be sure and note these restrictions: Fast Data Masker currently supports the following operators: <,<=,=, =>, > and LIKE (LIKE is not supported for flat files. It is only used for database masking).

     

    Cheers!
    Les