4 Replies Latest reply on Dec 5, 2019 7:11 AM by Nico Heinze

# Derive Value in a Group based on one column

Hi ,

I have the below data and i would like to populate a column as shown .

In a group , first record should get compared with all other records and relationship with other records should be stored in output column. then second records should get compared with renaming records in group but not with first record .

Source data -

 Customer_ID Group_ID Country Relationship 111 1 IND 112|113 112 1 UK 113 113 1 UK NA 114 2 UK 115|116 115 2 IND 116 116 2 IND NA 117 3 IND 118|119|120|121 118 3 IND 119|120|121 119 3 UK 120|121 120 3 UK 121 121 3 IND NA

Lookup data

 IND-IND 50 IND-UK 51 UK-UK 51

Target Data

 Customer_ID Group_ID Country Relationship Type Of Rel (Output) 111 1 IND 112|113 50|50 112 1 UK 113 51 113 1 UK NA NA 114 2 UK 115|116 51|51 115 2 IND 116 50 116 2 IND NA NA 117 3 IND 118|119|120|121 50|51|51|50 118 3 IND 119|120|121 51|51|50 119 3 UK 120|121 51|51 120 3 UK 121 51 121 3 IND NA NA
• ###### 1. Re: Derive Value in a Group based on one column

Not clear to me how to derive these numbers (50 / 51) from the input data. Please describe in plain text with one sample case with four input records, that should make the thing clear.

In general you should try to get hold of the Loop Transformations for PowerCenter which I've presented at Informatica World in 2016. They will enable you to iterate through the whole list of relationships per input record.

Regards,

Nico

• ###### 2. Re: Derive Value in a Group based on one column

I have given a Lookup table in example above, lates say 111 is from IND and 112 is from UK so relationship between these two is 51.

What do you mean by loop transformations? Which transformations comes under it.

Regards

Abhidha

• ###### 3. Re: Derive Value in a Group based on one column

But the LKP is performed based on what values???

When I look at entries 111 and 112 and 113, then they have these countries associated (according to your sample data): IND, UK, UK.

For me that means that a relation 111 - 112 resp. 111 - 113 must be IND - UK in both cases. Which according to the table above means 51. However, your sample output lists 50, not 51. That's why I'm so puzzled about these values.

Regarding the Loop Transformations a short bit of history and info:

During Informatica World 2016 I was allowed to present preliminary versions of two transformations that I've programmed for PowerCenter, a Loop Begin (LB) and a Loop End (LE) transformation. All transformations between LB and associated LE can be executed an arbitrary number of times (at least once), and you can set up the condition whether to repeat that loop or not using standard mapping logic (e.g. in an EXP).

In your case you have an unknown number of "relationships" in your source record. That's a typical example where the Loop Transformations come in handy.

Now the link to these transformations on the Informatica Marketplace doesn't exist anymore. The one link that still exists probably won't help because the version published there was a demo version (fully functional but extremely restricted in terms of ports which can be passed into and out of the transformation loop). You can still use them, but it's a little tricky.

So what you can do right now is to use an EXP and a Normalizer to split up the "Relationship" string into individual "relationship numbers". However, as always with the NRM, the number of repetitions is fixed. Meaning you will need to know in advance how many relationships may exist per source record.

But if you do know the maximum number of repetitions, then you can use an EXP to split up the Relationship string into individual numbers, followed by a NRM to "translate" these individual numbers into individual records; then you can perform the LKP you need to do in order to get those numerical values.

And finally you have to re-assemble those numbers into the target data you need.

It's not really difficult, but the fact that the number of repetitive ports in a NRM is fixed may make it unusable in your case. That's something that only you (resp. your organisation) can tell.

Having written that, probably the easiest way to go right now is to use a Java Transformation to split up this Relationship string into individual numbers. Then perform a LKP on these numbers, and use an AGG to re-assemble the target data.

Regards,

Nico

• ###### 4. Re: Derive Value in a Group based on one column

...Seems to be high time for me to re-post the Loop Transformations on the Informatica Marketplace. Informatica has never taken the time to take my work from 2016 and convert it into standard features of PowerCenter. Sad but true.

And I should finally take the time to build these transformations such that they can be used with arbitrary structures. The Java Transformations that I've built in 2016 have a fixed structure, and it's not too easy to extend it.

It would be good to know how many people are interested in using these Loop Transformations. Thanks in advance for your feedback.

Regards,

Nico