Active Lookup transformation has been evidently useful in power center from a long time. As we all aware that Power center 9 releases has upgraded lookup transformation to active, i.e it can return multiple values in the case of multiple matches. Lets see where is it useful and how could this be configured
Consider a simple applicable area in Finance domain. In real time a person can have multiple accounts / credit cards and many add on cards connected to it. So now if you are asked to generate a report which can evaluate risk for a particular person's account.In this case you will need to consider all his accounts, sub accounts and all the cards connected to a person.
Lets get into more here -Below is a sample record
So in above case. When you want to evaluate risk for this account. you would(have to) consider all these accounts rather any one. I want to call my risk engine for any problem with any of these accounts, so I want to generate a report or file in such a way that can detail all the accounts connected to a single person, which can be easily achieved through the new feature.
How to configure Active Lookup Transformation ?
- Create Lookup transformation by choosing from transformation tab
- Check Return all values on multiple match while creating it.
- Done, now it is an active transformation
- Point to note – this configuration cant be rolled back, a lookup configured to be an active transformation cant be an inactive one and vice versa
How to use Active Lookup Transformation in Mapping ?
- The above configuration gives you four different rows when the condition on relationID matches.
- Use an Expression, aggregator to transpose them all together to one row.
- The flow will look as below template
- Post alignment of these accounts, a risk engine can be called to decide the risk aligned with any of these accounts