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

    match

    sagar rahul

      hiii need a small help

       

      i am having certain names

      Anil Kumar

      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

       

      CUST_FULL_NAMEoutputCUST_MOBILE_NUMBERCUST_EMAILPOL_INSURER_NAMEname
      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
      A GIRI258AVULAGIRI@GMAIL.COMLASYA PRIYA 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:

                 

                MAP_CLEANNAMES:

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

                    Name,

                    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

                 

                and

                 

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