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

    Derive Value in a Group based on one column

    Abhidha P Seasoned Veteran

      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_IDGroup_IDCountryRelationship
      1111IND112|113
      1121UK113
      1131UKNA
      1142UK115|116
      1152IND116
      1162INDNA
      1173IND118|119|120|121
      1183IND119|120|121
      1193UK120|121
      1203UK121
      1213INDNA

       

      Lookup data

       

      IND-IND50
      IND-UK51
      UK-UK51

       

      Target Data

       

          

      Customer_IDGroup_IDCountryRelationshipType Of Rel (Output)
      1111IND112|11350|50
      1121UK11351
      1131UKNANA
      1142UK115|11651|51
      1152IND11650
      1162INDNANA
      1173IND118|119|120|12150|51|51|50
      1183IND119|120|12151|51|50
      1193UK120|12151|51
      1203UK12151
      1213INDNANA
        • 1. Re: Derive Value in a Group based on one column
          Nico Heinze Guru

          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.

           

          What's your PowerCenter operating system?

           

          Regards,

          Nico

          • 2. Re: Derive Value in a Group based on one column
            Abhidha P Seasoned Veteran

            Thanks Nico for reply, I was expecting your response since morning.

            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.

            Can you please share the link of your post which you mentioned above.

            Regards

            Abhidha

            • 3. Re: Derive Value in a Group based on one column
              Nico Heinze Guru

              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
                Nico Heinze Guru

                ...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