1 2 Previous Next 17 Replies Latest reply on May 17, 2021 12:17 AM by David Lopez Cruz

    Logic implementation in Informatica mapping

    Vindhya Giri New Member

      Hello,

       

      I have a informatica mapping in which csv is the source file for the target table.

       

      ABC_ID

      XYZ_CODE are the 2 fields I am looking at mainly.

       

      If ABC_ID in the csv file is more than once, I want to load them into the table by following priority order of XYZ_CODE.

      • NULL(Blank)
      • U
      • I
      • D

       

      Example:

      ABC_ID           XYZ_CODE

      1                         Null

      2                         I

      2                        U

      3                        I

      3                        D

       

      The result I want in the table is:

      ABC_ID           XYZ_CODE

      1                         Null

      2                        U

      3                        I

       

      The goal is to have only one record for each ABC_ID in the table based on the priority order.

       

      Could you please advise on how can I achieve this transformation in the mapping?

       

      Thanks for your help,

       

      Vindhya

        • 1. Re: Logic implementation in Informatica mapping
          David Lopez Cruz Guru

          Sorry, I couldn't understand your explanation about priority order of XYZ_CODE.

          I can see in the results you are getting last occurrence for value 2 in ABC_ID (U) and first value 3 in ABC_ID (I).

           

          Can you explain the logic behind this?

          • 2. Re: Logic implementation in Informatica mapping
            Vindhya Giri New Member

            Hi David,

             

            Thank you for looking into my question.

             

            I want to rank the data when it comes in and then load the target table with only with 1 row for each ABC_ID based on the XYZ_CODE.

             

            I want to the rank the column 'XYZ_CODE' in following order:

            Rank 1- Null

            Rank 2- U

            Rank 3- I

            Rank4- D

             

            example1:

            ABC_ID           XYZ_CODE

            1                         Null

            Since, ABC_ID '1' has only 1 XYYZ_CODE 'Null', I want to load this into the table.

             

            example2:

            ABC_ID           XYZ_CODE

            2                         I

            2                         U

            Since, ABC_ID '2' has 2 XYYZ_CODE's 'I' and 'U', I want to load only the row  'U' into the table.

             

            example3:

            ABC_ID           XYZ_CODE

            3                         U

            3                         I

            3                        D

            Since, ABC_ID '3' has 3  XYYZ_CODE's 'U,'I' and 'D', I want to load only 'U' into the table.

             

            example4:

            ABC_ID           XYZ_CODE

            4                         I

            4                        D

            Since, ABC_ID '4' has 2 XYYZ_CODE's 'I' and 'D', I want to load only the row  'I' into the table.

             

            example5:

            ABC_ID           XYZ_CODE

            5                         I

            Since, ABC_ID '5 has 1 XYYZ_CODE 'D', I want to load  the row  'I' into the table.

             

            Thank you

             

            Vindhya

            • 3. Re: Logic implementation in Informatica mapping
              David Lopez Cruz Guru

              Check if it helps to keep the values from previous rows and do some comparison with them as indicated in this KB:

              9034

              • 4. Re: Logic implementation in Informatica mapping
                Nico Heinze Guru

                There are several distinct approaches to this in PowerCenter, some easy to implement, some pretty complex.

                 

                What David suggests (though - in my personal opinion - this particular example is not as clear as it could be) is to "aggregate" the counters of the four different XYZ codes on your own in an Expression transformation. This works fine as long as you have the input data sorted by ABC_ID. No problem on its own (after all there's a pretty good Sorter transformation available in PowerCenter), it's just worth mentioning.

                 

                An alternative (which also works better with sorted input) is to use an Aggregator transformation followed by an Expression transformation.

                The AGG (group-by port is ABC_ID) counts all records with Isnull( XYZ_CODE), all records with XYZ_CODE = 'U', XYZ_CODE = 'I', and XYZ_CODE = 'D'. These four COUNT()-values are output ports of the AGG.

                The EXP now checks whether the count of NULL values is > 0; if so, set the output port XYZ_CODE to NULL.

                If the count of NULL values is = 0, then check for a count of 'U' values > 0; if so, set the output to 'U'.

                Otherwise check whether the count of 'I' values; if it's > 0, set the output to 'I'.

                Otherwise set the output to 'D'.

                This can be done in one single expression like this:

                 

                Decode( TRUE,

                  Cnt_NULL > 0, NULL,

                  Cnt_U > 0, 'U',

                  Cnt_I > 0, 'I',

                  'D')

                 

                The "Decode( TRUE,..." construction is like an IF... ELSEIF ... ELSEIF ... ELSE command in a programming language.

                 

                Regards,

                Nico

                1 of 1 people found this helpful
                • 5. Re: Logic implementation in Informatica mapping
                  David Lopez Cruz Guru

                  Yes, Nico, is true. I assumed the data is ordered, by both fields actually.

                  • 6. Re: Logic implementation in Informatica mapping
                    Vindhya Giri New Member

                    Hi Nico,

                     

                    Thank you for your feedback. I might need some additional guidance on achieving this.

                     

                    In this source file I have around 50 fields that I am pulling into the target table.

                    In the target table I want all the fields to be loaded but based on the priority of XYZ_CODE.

                     

                    1. From the source Qualifier, I would have to pull all the ports into the Aggregate transformation but need to checkbox the GroupBy for only the ABC_ID port.

                    I have created 4 new ports in aggregate transformation as:

                     

                    Port Name               Expression

                    Cnt_NULL            Count(ABC_ID,XYZ_CODE='')

                    Cnt_U                   Count(ABC_IDE,XYZ_CODE='U')

                    Cnt_I                    Count(ABC_ID,XYZ_CODE='I')

                    Cnt_D                   Count(ABC_ID,DXYZ_CODE='D')

                     

                    2. Next, I have pulled all the ports from  Aggregate transformation into Expression Transformation including the 4 new ports in agg transformation.

                    Created a new port 'Code_Out' in expression transformation with code

                    Decode( TRUE,

                      Cnt_NULL > 0, NULL,

                      Cnt_U > 0, 'U',

                      Cnt_I > 0, 'I',

                      'D')

                     

                    3. Created a filter transformation : pull all the ports from Exp Tranformation and in the filter properties, I gave

                    Code_Out= XYZ_CODE

                     

                    I ran the workflow it completed successfully but the target table is not getting loaded correctly. I tested couple ABC_ID's which have 2 XYZ_CODE's in the source. But, I do not see them in the target table.

                     

                    Could you please advise where can the issue be?

                     

                    Thank you much

                     

                    Vindhya

                    • 7. Re: Logic implementation in Informatica mapping
                      David Lopez Cruz Guru

                      You can select Verbose Data tracing level to see in your session log how the data is evolving in each steps.

                      Hope this can help you to figure out where the issue is.

                      • 8. Re: Logic implementation in Informatica mapping
                        Nico Heinze Guru

                        In this case the error is probably very easy to remedy:

                         

                        You wrote that you have set Count_NULL to this expression:

                          Count( ABC_ID, XYZ_CODE = '')

                        This will not work. Simply because a NULL value (read from the flat file source, as far as I understand it) is something different than an empty string (what you compare).

                        So you should change this port to this expression:

                          Count( ABC_ID, IsNull( XYZ_CODE))

                         

                        (And yes, I know that Oracle cannot clearly distinguish between NULL strings and empty strings, but PowerCenter does.)

                         

                        But that's indeed not the whole problem.

                        If I understood you right, you want to populate the other 48 fields with the values of the XYZ_CODE which has to be chosen according to the priority (first NULL values, then U, then I, last D).

                        Right?

                        If so, we may have a problem. The big question here is: can there be more than one instance of each XYZ_CODE per ABC_ID?

                        For example, could it be that for one ABC_ID there are two records with XYZ_CODE being NULL?

                        Or can there be more than one record per ABC_ID with XYZ_CODE = 'U' / 'I' / 'D'?

                         

                        If this is the case, then your requirement is not worded exactly enough. The point is this:

                        Let's assume for this example case that there are four records with ABC_ID = '123ABC'; two of these records have no XYZ_CODE (meaning this field is NULL), two have XYZ_CODE = 'I'.

                        How shall we decide which of the two records with a NULL in XYZ_CODE shall be chosen?

                         

                        Next example: let's assume that for ABC_ID = '456DEF' there are two records, both with XYZ_CODE = 'I'.

                        How shall we decide which one of these two records to forward to the target?

                         

                        I hope my questions are clear. If not, please ask again.

                         

                        Regards,

                        Nico

                        1 of 1 people found this helpful
                        • 9. Re: Logic implementation in Informatica mapping
                          Vindhya Giri New Member

                          Hi Nico,

                           

                          I have made change  to   Count( ABC_ID, IsNull( XYZ_CODE)) as suggested by you. Still seeing the same issue of target table not getting loaded with correct number of records.

                          I noticed that all the records will nulls are not getting loaded into the table and also the records with ID's having more then 1 XYZ_CODE are not getting loaded.

                          Example: records with below cases are not getting loaded.

                          ABC_ID            XYZ_CODE

                          567                         NULL

                          789                         I

                          789                         D                  

                           

                           

                          could it be that for one ABC_ID there are two records with XYZ_CODE being NULL? No

                          can there be more than one record per ABC_ID with XYZ_CODE = 'U' / 'I' / 'D'? No

                           

                          One ABC_ID can have multiple XYZ_CODE's but the code will not be the same.

                          ABC_ID cannot have 2 rows with Null code or 2 rows with 'U' / 'I' / 'D' code

                           

                           

                          There cannot be cases like below:

                          Let's assume for this example case that there are four records with ABC_ID = '123ABC'; two of these records have no XYZ_CODE (meaning this field is NULL), two have XYZ_CODE = 'I'.

                          How shall we decide which of the two records with a NULL in XYZ_CODE shall be chosen? There cannot be  2 nulls for same ID and there cannot be 2 'I's for the same ID.

                           

                          Next example: let's assume that for ABC_ID = '456DEF' there are two records, both with XYZ_CODE = 'I'.

                          How shall we decide which one of these two records to forward to the target? There cannot be 2 'I's for the same ID.

                           

                          Please advise.

                           

                          Thank you

                          Vindhya

                          • 10. Re: Logic implementation in Informatica mapping
                            Nico Heinze Guru

                            Please post a screenshot of the mapping, we will ask for more details as needed.

                            I think that's the fastest way to get to the core of the trouble.

                             

                            Thanks,

                            Nico

                            • 11. Re: Logic implementation in Informatica mapping
                              user126898 Guru

                              Can you try to use the rank transformation.

                               

                               

                              I have mocked up the flat file on your example.

                               

                               

                              In the expression we assign a rank to the XYZ_CODE field

                               

                               

                              Then create the rank transform

                               

                               

                              ranking from the bottom up.

                               

                              The output looks exactly what you are looking for.

                               

                               

                               

                              thanks,

                              Scott

                              1 of 1 people found this helpful
                              • 12. Re: Logic implementation in Informatica mapping
                                Vindhya Giri New Member

                                Hi Scott,

                                 

                                I tried your suggestion of rank transformation.

                                1. In Expression--> Added a new port 'Rank_XYZ_CODE' and gave the expression

                                IIF(ISNULL(XYZ_CODE),1,

                                IIF(XYZ_CODE= 'U',2,

                                IIF(XYZ_CODE= 'I',3,

                                IIF(XYZ_CODE= 'D',4,5)

                                )

                                )

                                )

                                 

                                2.Created a Rank Transformation and pulled all the fields from Expression to rank and also the new port 'Rank_XYZ_CODE' and check mark the 'R' for the new port

                                 

                                In the properties of Rank Transformation--> I have made the following selections

                                 

                                When I ran the workflow session, 0 records were inserted into the table.

                                Could you please advise on this?

                                 

                                Thanks

                                Vindhya

                                • 13. Re: Logic implementation in Informatica mapping
                                  Nico Heinze Guru

                                  The Ranker is a good trick, Scott. To be honest I always forget that there is such a transformation available.

                                  And yes, that's probably the easiest approach for this task.

                                   

                                  Again, please post a screenshot of your mapping so that we can help you to find the mistake in the implementation.

                                   

                                  Regards,

                                  Nico

                                  • 14. Re: Logic implementation in Informatica mapping
                                    Vindhya Giri New Member

                                    Hi Nico,

                                     

                                    I am sorry, I will not be able to provide the screenshot of original mapping due to company policy.

                                    I will work on creating one with alias names and get back to you with screenshot.

                                     

                                    Since it was a production issue, I have implemented the logic by using rank through a database view to delete the records(based on the priority)  as a second step after the table is loaded completely first.

                                     

                                    But, yes, I would definitely want to learn on what mistakes I have made. I am sure I will come across similar requirement in future.

                                     

                                    Thank You much for helping me understand the process.

                                     

                                    Regards,

                                    Vindhya

                                    1 2 Previous Next