13 Replies Latest reply on Jun 29, 2020 1:20 AM by Dharmendra Reddy Nara

    How to do consolidation with most recent not null value?

    Dhanya Chandran New Member

      Hi all,


      I am trying to consolidate a record in Informatica 9.6.0 Developer tool. Here we faced a situation that we need to consolidate our record based on a given field which is the Most Recent Update Date field. Now if this most recent update field contains a null, the value should be checked with other records if we found a value in that column, the final record will fill the value from there, an example is as follows


      Suppose

       

       

      Cluster ID Name PhoneNumber SSN Last_Update_DAte
      1 Danny 44567 1124    26-10-2011
      1 Dhan  12346              25-10-2012
      1 Dan    11245              25-10-2013
      1 Dyan   78934 11246  11-11-2012

       

      Then the result will be


      Cluster ID Name PhoneNumber SSN Last_Update_DAte
      1 Dan 78934 11246 25-10-2013

       


      how can we achive this in consolidation transformation. Please help with the scenario, we havent find any function here in consolidation based on this case..


      Thanks

        • 1. Re: How to do consolidation with most recent not null value?
          New Member

          Hi Dhanya,

           

          We are also facing the same requirement. If you would have found solution to this could you please share how to achieve this?..

          Thanks in advance.

           

          - Shriram

          • 2. Re: How to do consolidation with most recent not null value?
            Guru

            In such scenario, why can't we filter records having null update date and then pass the rest of o/p to Consolidation Tx? Strategy with 'Maximum' on update date field can be configured to get the latest recent update record.

            • 3. Re: How to do consolidation with most recent not null value?
              Sasikumar Sundaram Active Member

              Dhanya,

              Did you try using advanced strategies in consolidation transformation like CONSOL_MAX? I believe this can return the expected results.

               

              Thanks!!

              Sasi

              • 4. Re: How to do consolidation with most recent not null value?
                New Member

                Did you guys sorted out how to achieve this if so do lemme know

                • 5. Re: How to do consolidation with most recent not null value?
                  Abhishek Singh Active Member

                  Hi Dhanya,

                   

                  Please let me know if you got the solution to this issue. I am also having the same scenario.

                  • 6. Re: How to do consolidation with most recent not null value?
                    Syed Muzamil Guru

                    Hi Dhanya,

                     

                    You can use Modal_Exact Row Based strategy to achieve this requirement.

                     

                     

                     

                    If you are still looking for any specific tuning in the output results, you can always go with custom logic in the Advanced options.

                     

                    Hope this helps.

                     

                    Thanks,

                    Syed

                    • 7. Re: How to do consolidation with most recent not null value?
                      Abhishek Singh Active Member

                      Hi Syed,

                      Thank you for the response.

                       

                      Just using the MODAL_EXACT row based strategy will not solve the purpose here as the requirement is not straightforward.

                      The survivorship rule has 2 parts:

                       

                      1). To consolidate the record based on Most Recent Update_Date field. That means, the record with most recent Update_Date should be consolidated as Survivor record

                       

                      2) Now if any data value from the consolidated record is null, then the value should be checked with other duplicate records from the same cluster and update the null data value with the most frequent non-blank value for that column in the same cluster.

                       

                      Please let me know how we will write this logic in the Advanced Strategy of Consolidation Tx.

                      • 8. Re: How to do consolidation with most recent not null value?
                        Dharmendra Reddy Nara Active Member

                        Abhishek Singh

                         

                        Correct me if my understanding toward the requirement is correct or wrong.

                        You want to check for last update date and which ever record has recent update date then all the contents of that record should be shown for that cluster_id. If Last_Update_Date is NULL for all the records in cluster then you want to depend on the other record in the group(Say PhoneNumber field) and if phone number field has a value then need to print that record values for that cluster.

                         

                        I don't think the requirement which your asking for is possible with consolidation transformation. But I can achieve the same using Sorter and Aggregator.

                         

                        For Example:

                        Cluster_ID,Name,PhoneNumber,SSN,Last_Update_DAte

                        1,Danny,44567,1124,26-10-2011

                        1,Dhan,12346,,25-10-2012

                        1,Dan,11245,,25-10-2013

                        1,Dyan,78934,1124,11-11-2012

                        2,Dyan,78934,1124,

                        2,Dan,11245,,

                        2,Dhan,12346,,

                         

                        Result:

                        1 Dan 11245 <null> 2013-10-25 00:00:00.0

                        2 Dyan 78934 1124 <null>

                         

                         

                        Solution:

                        Send the records to sorter transformation and use below configuration. If PhoneNumber column also has all NULL's then you can add one more column in sorting.

                        In Advance properties check Null Treated Low box.

                         

                        Now send the output of sorter to aggregator and group by on custer_id.

                         

                        Please let me know if your requirement is different

                        • 9. Re: How to do consolidation with most recent not null value?
                          Abhishek Singh Active Member

                          Hi Dharmendra,

                           

                          I think you misunderstood the problem here or rather the original question was put in the wrong way. Let me simplify this.

                           

                          original post content - "Now if this most recent update field contains a null, the value should be checked with other records if we found a value in that column, the final record will fill the value from there"

                           

                          What it actually means - If any attribute from the most recent update field contains null, then the value for that particular attribute should be checked with other duplicate records in that cluster and update the missing value for that attribute.

                           

                           

                          Consider the above example.

                          In the above cluster, the Record ID - 1 will be considered as master record as it has the latest Last modified Date.

                          Since, the master record (Record ID - 1) contains null value for the phone attribute, this should be enriched from other 2 duplicate record (Record ID 2 & 3) from the same cluster.

                           

                           

                          1. In a duplicate set, if one or more attribute in master record has NULL:
                            1. If only one duplicate record with valid data is found, then the NULL value field will be replaced with the value from the duplicated record.
                            2. If multiple duplicate records each with valid value are found, then the NULL will be replaced with the value having the second newest Last modified Date from other duplicated records.

                                                               

                          I think this can be implemented using the consolidation - advance strategy.

                           

                          Please let me know if you need further clarification. I am looking for the solution quite urgently as I have to implement this for one of the project requirement.

                           

                          Much appreciate ! Thank you.

                           

                          Regards,

                          Abhi

                          • 10. Re: How to do consolidation with most recent not null value?
                            Sunil Kumar Singh Seasoned Veteran

                            Hi Abhi,

                            Did you find any solution to serve the scenario mentioned ?

                             

                            Best Regards,

                            Sunil

                            • 11. Re: How to do consolidation with most recent not null value?
                              Dharmendra Reddy Nara Active Member

                              Hi Sunil,

                               

                              Above requirement isn't possible using consolidation transformation. I have tested with multiple advance strategie codes but didn't achieve it.

                              Also just to get the records of the row which has latest modified date is very tough and you need to build logic using other transformation before sending data to consolidation transformation.

                               

                              Kind Regards,

                              Dharmendra Reddy.

                              • 12. Re: How to do consolidation with most recent not null value?
                                Sunil Kumar Singh Seasoned Veteran

                                Hi Dharmendra,

                                 

                                Thanks for your reply.

                                 

                                I had consulted one of my friend and as per his suggestion, I was able to achieve the desired outcome using aggregator Tx.

                                 

                                Solution as learnt from him and tested:-

                                Sort the records with descending order of last update date.

                                Such that, within a cluster, the latest record becomes the first row.

                                After sorting, pass all records to an aggregator tx, group by cluster ID.

                                For all the input ports for which latest non-null attribute is required to be survived, create a corresponding output port. say if input ports are email,fax, then create two output ports o_email and o_fax.

                                For the output ports configure expression using aggregator functions as,

                                o_email = FIRST(email) and o_fax = FIRST(fax).

                                Similarly, need to create output ports for all input ports. (If there are more for which same outcome is required).

                                The FIRST function selects the first non-null attribute value within that port. So, considering the order are sorted with recency and if top record contains null value, the FIRST function navigates to next row and henceforth to find the first non-null value. Similarly, there is a LAST function as well to perform the exactly opposite scenario(to select Last non-null value for that port).

                                 

                                So, the aggregator output now contains single record from each group(clusterID) with latest non-null attributes.

                                 

                                Best Regards,

                                Sunil

                                1 of 1 people found this helpful
                                • 13. Re: How to do consolidation with most recent not null value?
                                  Dharmendra Reddy Nara Active Member

                                  Hi Sunil,

                                   

                                  I have suggested the similar logic "I don't think the requirement which your asking for is possible with consolidation transformation. But I can achieve the same using Sorter and Aggregator." on May 25th 2020 response.

                                   

                                  But the only difference is that you have created new ports and use FIRST or LAST functions but I have columns which may contain null data in sort key and In Advance properties check Null Treated Low box.

                                   

                                  Using FIRST/LAST should be the correct method as adding more columns in sort key may impact the final values.

                                   

                                  Thank you for replying with the logic. I can see 3 or 4 threads having same concerns. Your input will be helpful.

                                   

                                  Kind Regards,

                                  Dharmendra Reddy