Skip navigation
Contact Us
2857 Views 3 Replies Latest reply: Feb 14, 2013 10:33 PM by Brian Chung RSS
Gary Mason Newbie 1 posts since
Feb 9, 2011
Currently Being Moderated

Oct 5, 2011 9:51 AM

Standardizer transformation with Reference table

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?

 

thanks

 

Gary

  • Ravikumar Sanjeevi Expert 533 posts since
    May 7, 2011
    Currently Being Moderated
    Oct 5, 2011 12:16 PM (in response to Gary Mason)
    Standardizer transformation with Reference table

    Hi Gary

     

     

     

    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

     

    Regards,
    Sanjeevi

  • David Jordan InfaEmp 1 posts since
    Dec 16, 2007
    Currently Being Moderated
    Oct 6, 2011 3:17 AM (in response to Gary Mason)
    Standardizer transformation with Reference table

    Hi Gary,

     

    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.

     

    Regards

    David

  • Brian Chung Novice 27 posts since
    Dec 28, 2011
    Currently Being Moderated
    Feb 14, 2013 10:33 PM (in response to Gary Mason)
    Standardizer transformation with Reference table

    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.

     

    Cheers,

     

    Brian

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points