I believe this is what you are looking for:
Issue:
Do multiple simultaneous lookups on a single translation table. Multiple source columns need to be mapped to the same reference column and the translated(standard) value needs to be passed to target.
The job design can be:
Code: Select all
DB2 Src for rule table--->Hashed File(Rule Table loaded into HF)
|(Lkp1)|(Lkp2)|(Lkp3)
| | |
| | |
Source------------------------>Transformer -------------->Target Table or File.
LKP1: Lookup for Gender
LKP2: Lookup for State
LKP3: Lookup for Cntry_Code
The lookup logic is as follows:
Primary input to ref transformer:
Source_ID
Gender
State
Cntry_Code
Lkp1:
Source_ID [Key Yes] <--(Key Derivation)-- Primary.Source_ID
Source_Value [Key Yes] <--(Key Derivation)-- Primary.Gender
Cleansed_Value [key No] (Map to Target as Gender)
Lkp2:
Source_ID [Key Yes] <--(Key Derivation)-- Primary.Source_ID
Source_Value [Key Yes] <--(Key Derivation)-- Primary.State
Cleansed_Value [key No] (Map to Target as State)
Lkp3:
Source_ID [Key Yes] <--(Key Derivation)-- Primary.Source_ID
Source_Value [Key Yes] <--(Key Derivation)-- Primary.Cntry_Code
Cleansed_Value [key No] (Map to Target as Cntry_Code)
Output of transformer:
Source_ID<--(Derivation)--Primary.Source_ID
Gender<--(Derivation)--Lkp1.Cleansed_Value
State<--(Derivation)--Lkp2.Cleansed_Value
Cntry_Code<--(Derivation)--Lkp3.Cleansed_Value
IHTH