Topic
  • 3 replies
  • Latest Post - ‏2014-01-15T18:01:07Z by Matthew_Simons
Ducatiman
Ducatiman
58 Posts

Pinned topic Processing a column based on 'After masked' fields

‏2013-07-08T20:56:01Z |

something simple (or not):

Is there a way to mask a field based upon the value of another field that was just masked in the same column map?

 

We have three fields FIRSTNAME, LASTNAME, FULLNAME.

The column map uses HASH_LOOKUP on FIRST and LAST. Those results need to be placed into the FULLNAME field.

I can only think of 2 pass conversion.

step 1: Convert first and last name fields

step 2: Concatenate FULLNAME field (FULLNAME = FIRSTNAME || ' ' || LASTNAME

I tried a column map procedure but that holds onto the original names

 

OPTIM 9.1 fp3

Windows 2008 R2

Oracle data source

 

  • nmullin
    nmullin
    10 Posts

    Re: Processing a column based on 'After masked' fields

    ‏2013-07-09T19:18:43Z  

    The way that I have done that type of requirement is via 2 pass conversion.

  • willem_tell
    willem_tell
    1 Post

    Re: Processing a column based on 'After masked' fields

    ‏2013-12-06T08:41:39Z  
    You could use the following multi-column HASH_LOOKUP solution :
     
    Create a Lookup Table with something like the following layout :
     
       CREATE TABLE LOOKUP_NAME
       (
        SEQ              NUMBER(010)  ,
        LOOKUP_FIRSTNAME VARCHAR2(025),
        LOOKUP_LASTNAME  VARCHAR2(035),
        LOOKUP_FULLNAME  VARCHAR2(060),
        CONSTRAINT "PK_LOOKUP_NAME" PRIMARY KEY ("SEQ")
       );
     
    Fill the Lookup Table with something like this :
     
    '1','Barack', 'Obama', 'Barack Obama'
    '2','Nelson', 'Mandela', 'Nelson Mandela'
    ...
    (Dont forget to specify values for SEQ = -3/-2/-1 !!!)
     
    Create a Column Map (for your table) and specify the following HASH_LOOKUP function for the column FULLNAME (in your table) :
     
    HASH_LOOKUP(FULLNAME,TRIM=(,\U),DEST=(FIRSTNAME,LASTNAME,FULLNAME),LOOKUP_NAME(SEQ,VALUES=(LOOKUP_FIRSTNAME,LOOKUP_LASTNAME,LOOKUP_FULLNAME)),PRESERVE=(FIRSTNAME(NULL,SPACES,ZERO_LEN),LASTNAME(NULL,SPACES,ZERO_LEN),FULLNAME(NULL,SPACES,ZERO_LEN)))

    For clarity : 

    HASH_LOOKUP(FULLNAME,
                TRIM=(,\U),
                DEST=(FIRSTNAME,LASTNAME,FULLNAME),
                LOOKUP_NAME(SEQ,VALUES=(LOOKUP_FIRSTNAME,LOOKUP_LASTNAME,LOOKUP_FULLNAME)),
                PRESERVE=(FIRSTNAME(NULL,SPACES,ZERO_LEN),LASTNAME(NULL,SPACES,ZERO_LEN),FULLNAME(NULL,SPACES,ZERO_LEN))
               )
     
    Notice that in your Column Map :
     - Your source column FULLNAME will have the Status = 'Lookup Function'.
     - You have to set your source column FIRSTNAME to blank, it will have the Status = 'Not Used'.
     - You have to set your source column LASTNAME to blank, it will have the Status = 'Not Used'.
     
     
     
    Updated on 2013-12-06T08:53:13Z at 2013-12-06T08:53:13Z by willem_tell
  • Matthew_Simons
    Matthew_Simons
    27 Posts

    Re: Processing a column based on 'After masked' fields

    ‏2014-01-15T18:01:07Z  
    You could use the following multi-column HASH_LOOKUP solution :
     
    Create a Lookup Table with something like the following layout :
     
       CREATE TABLE LOOKUP_NAME
       (
        SEQ              NUMBER(010)  ,
        LOOKUP_FIRSTNAME VARCHAR2(025),
        LOOKUP_LASTNAME  VARCHAR2(035),
        LOOKUP_FULLNAME  VARCHAR2(060),
        CONSTRAINT "PK_LOOKUP_NAME" PRIMARY KEY ("SEQ")
       );
     
    Fill the Lookup Table with something like this :
     
    '1','Barack', 'Obama', 'Barack Obama'
    '2','Nelson', 'Mandela', 'Nelson Mandela'
    ...
    (Dont forget to specify values for SEQ = -3/-2/-1 !!!)
     
    Create a Column Map (for your table) and specify the following HASH_LOOKUP function for the column FULLNAME (in your table) :
     
    HASH_LOOKUP(FULLNAME,TRIM=(,\U),DEST=(FIRSTNAME,LASTNAME,FULLNAME),LOOKUP_NAME(SEQ,VALUES=(LOOKUP_FIRSTNAME,LOOKUP_LASTNAME,LOOKUP_FULLNAME)),PRESERVE=(FIRSTNAME(NULL,SPACES,ZERO_LEN),LASTNAME(NULL,SPACES,ZERO_LEN),FULLNAME(NULL,SPACES,ZERO_LEN)))

    For clarity : 

    HASH_LOOKUP(FULLNAME,
                TRIM=(,\U),
                DEST=(FIRSTNAME,LASTNAME,FULLNAME),
                LOOKUP_NAME(SEQ,VALUES=(LOOKUP_FIRSTNAME,LOOKUP_LASTNAME,LOOKUP_FULLNAME)),
                PRESERVE=(FIRSTNAME(NULL,SPACES,ZERO_LEN),LASTNAME(NULL,SPACES,ZERO_LEN),FULLNAME(NULL,SPACES,ZERO_LEN))
               )
     
    Notice that in your Column Map :
     - Your source column FULLNAME will have the Status = 'Lookup Function'.
     - You have to set your source column FIRSTNAME to blank, it will have the Status = 'Not Used'.
     - You have to set your source column LASTNAME to blank, it will have the Status = 'Not Used'.
     
     
     

    William Tell has the cleanest option, in my opinion.  Two other choices:

    1. Use one column map for the Convert that does FIRST and LAST and clears/truncates FULL.  Use a second column map for the Insert/Load that populates FULL with the concatention

    2. Same as option 1 above, but update with SQL after the fact instead of a second column map.

    Let us know which option you wound up choosing and why.

     

    Cheers,

    Matt