6 Replies Latest reply: May 22, 2018 5:20 AM by sagar rahul RSS


    sagar rahul

      hiii need a small help


      i am having certain names

      Anil Kumar



      anil k

      k anil

      kumar anil

      kumar a


      wanna search them in my CUST_FULL_NAME and POL_INSURER_NAME column with all combination of name given up if match then output should out as below


      anil(matching)Anil123AVULAGIRI@GMAIL.COMA GIRI
      cghj123AVULAGIRI@GMAIL.COMkumar a(yes present in list)kumar a
      anil kumar(yes present in list)anil kumar547AVULAGIRI@GMAIL.COMANUSHA ADAVI
      kumar anil (yes present in list)kumar anil445AVULAGIRI@GMAIL.COMGIRI ADAVI
      adfgh55AVULAGIRI@GMAIL.COManil kumar(yes present in list)anil kumar
        • Re: match
          sagar rahul

          want its expression

          • Re: match
            sagar rahul

            want it expression

              • Re: match
                Jonathan Dienst

                This is a data cleanup question as far as I can see,


                One way that works well is to create a mapping table of "dirty" names to "clean", like this:



                Mapping LOAD * Inline


                Name1, Name2

                Anil Kumar, Anil Kumar

                anil, Anil Kumar

                kumar, Anil Kumar

                anil k, Anil Kumar

                k anil, Anil Kumar

                kumar anil, Anil Kumar

                kumar a, Anil Kumar





                Add other dirty - clean mappings as required. This is an inline load, but you can load from a spreadsheet or a database table using the same logic.


                Then use the mapping when you load the name:


                LOAD ...


                    ApplyMap('MAP_CLEANNAMES', Name, Name) as CleanName,


                This is more extensible and easier to maintain than hardcoding the cleaning in the expression

              • Re: match
                Michele De Nardi

                if(march(upper(CUST_FULL_NAME),'ANIL KUMAR','ANIL','KUMAR','ANIL K','K ANIL','KUMAR ANIL','KUMAR A'),CUST_FULL_NAME,null()) as output




                if(march(upper(POL_INSURER_NAME),'ANIL KUMAR','ANIL','KUMAR','ANIL K','K ANIL','KUMAR ANIL','KUMAR A'),CUST_FULL_NAME,null()) as name