I'm trying to use a standardizer transform with a reference table to standardize employee titles. My reference table has three columns: ValidValue, SourceValue and Description, with ValidValue marked as Valid. A row has values like 'DR', 'Doctor, Dr', 'Transform to standard Doctor'. When I use the transform in a mapping, I just get the source values, no transform. Am I misunderstanding something about the reference table or the transform?
I general when we use the reference tables mean we have compare/validate against our each source records with reference table. So you may/can use the reference table as lookup and compare
Each source records if you find as valid then you can pass to your target. In case the records is invalid then need to change/transform the records with respective to the reference table
Check your Standardizer transformation to see if you have any delimiters defined - if you do then remove the delimiters and check to see whether you're getting you desired standardisations.
When creating a Standardizer Transformation on IDQ, you can choose how you wish the standardizer to behave. Normally I use it to replace a certain value with the "valid" value. Choose the appropriate delimiter in your strategy and you should see your source value replaced with your valid values.
When you export your standardizer into power center... thats when things get messy. The mapping that contains the standardizer needs to be exported along with it's designated reference table. indicate the directory of the table along with the integration service and in result, you will have a power center version of your mapping from idq but with the exception of your standardizer turning into a mapplet! (frustrating when you cant CHOOSE a reference source or which columns are valid or invalid...)
Now if your IDQ standardizer was an active transformation, ie. one input and outputing two valid values, you'll have to make two standardizers for your power center mapping (its easier to make two standardizer mapplets on IDQ and then exporting). Once you get your mapping to run successfully, you'll notice that your values are not being replaced.
My solution, reverse the column order in your reference table. ie. if your valid value was the second column, make it the first column and re-run the mapping and it should return the correct value. it took a bit of time to experiment a little especially when the mapplet doesnt really give much detail as to how the standardizer is set up to behave.
Oh, I am currently using 9.1 hotfix 3.