1 2 Previous Next 16 Replies Latest reply on Sep 19, 2021 2:26 PM by Ste Frdric

    IDQ - Comparison Transformation

    Abhidha P Guru

      Hi All,

       

      I am using comparison transformation in my mapping to compare LAST_NAME in one table but it always returns 1 for all the records , means all the records has duplicate  which is not correct . Please see the attached screenshot for more detail and data.

      Let me know what is going incorrect here?

        • 1. Re: IDQ - Comparison Transformation
          user186817 Guru

          Hi Abhidha,

           

          In your screenshot, same column from your source is linked to both inputs in your Comparison transformation.

          What your are doing is comparing identical strings, so the output your are getting is correct.

           

          If you are trying to compare different records, this is not going to happen with that transformation.

          This is a passive transformation, which basically means it only deals with ports from the same record.

           

          What's your use case? What are you trying to accomplish?

           

          Regards,

          Lluís

          • 2. Re: IDQ - Comparison Transformation
            Abhidha P Guru

            Hi Lluís ,

             

            I think you are correct. As per Informatica comparasin transformation is like match transformation.

            I am trying to get duplicates in Last Name column using a rule and I would like to apply it on a profile.

            That is why I have connected same column in one input port.

            • 3. Re: IDQ - Comparison Transformation
              user186817 Guru

              Hi Abhidha,

               

              Still not 100% clear to me what you want to do.

               

              A simple profile on LAST_NAME column will tell you if you have duplicates.

              Then, analyzing the results, you can find in the 'Values' view which ones have a 'Frequency' higher than 1.

              If you double click that particular value, you will get the full record in the 'Data Viewer'.

               

              If that is not what you are looking for, please elaborate your need so we can try to find a better solution.

               

              Regards,
              Lluís

              • 4. Re: IDQ - Comparison Transformation
                Abhidha P Guru

                I would like to creaate a rule which can check on multiple columns in a table and tell about the duplicate.

                Like

                If I pass following 3 columns as input to rule , it should return 1 if it find a duplicate and 0 if it does not find any duplicate.

                 

                First name , last name , mobile

                • 5. Re: IDQ - Comparison Transformation
                  user186817 Guru

                  Hi Abhidha,

                   

                  I believe you could easily achieve that with a lookup to the same table you are profiling.

                  Lookup Condition should be:

                  1. FIRST_NAME = FIRST_NAME_lkp
                  2. LAST_NAME = LAST_NAME_lkp
                  3. PHONE = PHONE_lkp
                  4. ID != ID_lkp (this is to avoid false match)

                   

                  Then, you can add an expression to evaluate the lookup output.

                  When lookup output is null, you assign 0 and 1 when there's a match.

                   

                  Regards,

                  Lluís

                  2 of 2 people found this helpful
                  • 6. Re: IDQ - Comparison Transformation
                    user126898 Guru

                    Not sure a lookup would work.  What I know will work as I just mocked it up with your use case is an expression and aggregator. 

                     

                    In the first expression to assign a output column to 1.

                     

                    In the aggregator you group by the 3 fields and sum the count field.  Anything that has a count > 1 there is duplicates for first, last and mobile.  I didnt add it but you cn add a third expression to check the count and assign a value.

                     

                     

                    This can be wrapped in a mapplet and validated as a rule for you to use in the profile.

                     

                    Thanks,

                    Scott

                     

                    Now the screen shots are from IICS but the same applies to IDQ in on-prem (I didnt want to spin up our IDQ server)

                     

                     

                     

                     

                    source data:

                     

                    • 7. Re: IDQ - Comparison Transformation
                      user186817 Guru

                      Hi Scott,

                       

                      Abhidha wants to create a rule, you can't 'Validate as Rule' a mapplet containing an Aggregator transformation.

                       

                      Regards,

                      Lluís

                      • 8. Re: IDQ - Comparison Transformation
                        Abhidha P Guru

                        Hi Scott,

                         

                        As Lluís said , I want to create a rule which I can use with profile. Aggregator is an active transformation which can not be used in a rule.

                        • 9. Re: IDQ - Comparison Transformation
                          Abhidha P Guru

                          Thanks for the alternative solution. I will try it out.

                          But looks like comparison transformation is not for identifying the duplicates like match transformation.

                          • 10. Re: IDQ - Comparison Transformation
                            user186817 Guru

                            Hi Abhidha,

                             

                            Is your issue solved? Were you able to get the expected results?

                             

                            Regards,
                            Lluís

                            • 11. Re: IDQ - Comparison Transformation
                              Ste Frdric New Member

                              Hi user186817

                              I am having the same problem with being limited in what i can do with a rule in IDQ. Can you please explain how  this pulls duplicates with the lookup? Can you please explain  how this would identify duplicates (lookup) doesn't it identify non duplicates as well (only 1 record)? How can I get those only with more than one record?, please?

                               

                              Thank you

                              how can a lookup find duplicates?

                              • 12. Re: IDQ - Comparison Transformation
                                user186817 Guru

                                Hi Ste Frdric,

                                 

                                In that particular case, table contains same "first_name" + "last_name" + "phone_number", but with different "ID".

                                So, if you build a lookup with the conditions I propose:

                                 

                                1. FIRST_NAME = FIRST_NAME_lkp
                                2. LAST_NAME = LAST_NAME_lkp
                                3. PHONE = PHONE_lkp
                                4. ID != ID_lkp (this is very important to avoid false match)

                                 

                                when you get any output from the lookup that is not null, that means there's another record with same "first_name" + "last_name" + "phone_number" in the table. Keep in mind we have added ID != ID_lkp to avoid matching the input record with itself.

                                 

                                Here we are not trying to find all possible duplicates, just identifying if there's at least one.

                                 

                                Hope that helps to understand the proposed solution.

                                 

                                Regards,
                                Lluís

                                • 13. Re: IDQ - Comparison Transformation
                                  Abhidha P Guru

                                  Hi,

                                   

                                  I have tried the lookup method , it is working fine but the only problem is you can not reuse this rule because lookup table/columns are fix in lookup transformation.

                                  I found another way which is suggested by Informatica is that create a LDO and perform the required tasks in it and create the profile using this LDO.

                                  Using LDO we can identify duplicates using source -> key generator -> match -> target

                                  • 14. Re: IDQ - Comparison Transformation
                                    Ste Frdric New Member

                                    Thank you very much for this solution, Abhidha... this is great news for me.

                                    1 2 Previous Next