In CSV\Text Files, need to change the name format and take the Key and then apply masking function

Document ID : KB000008391
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Need a new function to create a synthetic 13 character name.

 

Parameters/Criteria: 

In CSV\Text Files, need to change the name format, take the resulting key, and then apply masking function.

 

Expected Functionality: 

1. Input file contains the name in format LASTNAME, FIRSTNAME MIDDLENAME

Eg: ARTEMIS, KELVIN M

2. Format the Data as FIRSTNAME MIDDELNAME LASTNAME

Eg: KELVIN M ARTMEMIS

3. Replace any special characters present in the data using REGEXPREPLACE,"[''(),/ ._-]{1,}" 

4. Take the substring of the data – 13 characters 

Eg: KELVINMARTMEM

 

Using the functions currently available in Fast Data Maker, the following conditions were created in the map file:

1. WHERE, "NAME_LFM<>''"

2. Using Combinevals function

3. Applying REGEXPSUBSTR,"[^,]+",1,2      KELVIN M

4. Fixed – Space

5. Applying REGEXPSUBSTR,"[^,]+",1,1      ïƒ¨ ARTEMIS

6. Result of Combinevals ʕĬ KELVIN M ARTMEMIS

7. WHERE,"NAME_LFM<>''" 

8. Applying REGEXPREPLACE,"[''(),/ ._-]{1,}"  - 

9. WHERE,"NAME_LFM<>''"

10. Applying SUBSTR,1,13

 

Note: After executing above steps, Multiple Where conditions of same field is not satisfying. 

 

Input Data: 

ARTEMIS, KELVIN M

For getting the key we need to change the name format and then obfuscate.

KELVIN M ARTEMIS

KELVINMARTEMI  - as this is the KEY

Apply Masking Function and output is generated as  “JOHN, SMITH K”

 

Is there a way to do this or does it require a new function?

Resolution:

You will need to ask Support for a version of Fast Data Masker 4.7.33.0 or above.  If your request is after September 28, 2017, please ask if this fix is in the latest GA. You can open a support case by going to https://www.ca.com/us/services-support/ca-support/contact-support.html. You can download the latest version of Test Data Manager, which contains the latest FDM version, by following directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html

 

The use of COMBINEVALS or CONCAT is not the way to achieve the data you want because each line concatenates the value before it, so you would have the same name repeated multiple times.

The patch contains a new function called MOVETOKEN, which has 2 optional parameters (created as issue US384921).

1) Move first token to last- Y or N if N then the last token is moved to the start (default is Y).

2) Delimiter- if not supplied then defaults to space, otherwise you could enter a comma, if for example the data is in the format of CSV.

 

You can achieve the data you want in a couple of ways

  1. Run 4 successive masks
  2. Run 1 mask with a postscript

 

The second option is the easiest and more performant way of getting the data you want.

The SQL in the file is

UPDATE TRAVEL.PEOPLE_TEST2 SET FULL_NAME = REPLACE(FULL_NAME, ',', '');

UPDATE TRAVEL.PEOPLE_TEST2 SET FULL_NAME = REPLACE(FULL_NAME, ' ', '');

UPDATE TRAVEL.PEOPLE_TEST2 SET FULL_NAME = SUBSTR(FULL_NAME, 1, 13);

 

The syntax may be different if you are transacting against a different database other than Oracle.

If you then needed to run a HASHLOV on this value, you would need to run a separate mask.