1 Reply Latest reply: Feb 22, 2016 5:55 PM by Stefan Wühl RSS

    Querying for name spelled multiple ways in 3 columns

    Carol Greenwald

      I am new to Qlik and need to write a query in QlikView 9 that searches 3 columns for employee name.  The data is coming in from an excel download every month and the first search to be done will be to compare employee name from another spreadsheet.

       

      The query will have to search at least 3 columns and compare employee name from another spreadsheet for confirmation that the user belongs to the correct group.  A major challenge is that employee name has no continuity on the monthly download and this cannot be corrected.  Employee can be identified by FName LName or LName FName, email address or user id.  Another problem is that the name may be spelled incorrectly.

       

       

      Example of the data to be queried:

       

      Thanks for your help in advance.

       

      Bill Detail

      Description 1

      Description 2

      OLVE oil

      Daisy Duck

      Duck, Daisy S

      Olive Oil

      Popeye

      1. Oil.Olive@WB.net
      2. Mouse.Mickey

      Mickey M

      Mickey M.

      1. Daisy.Duck@WWW.com

      Daisy S. Duck

      1. Sprat.Jack@WWB.com

      Mouse, Mickey

      Jack Sprat

      Jack Smyth

      Jck Sprat

      Duck, Daisy S

      Jack Smith

      Dduck

      Olive Oil

      Daisy S. Duke

      1. Jack.Smith@NBC.com

      Jack Smith

      Mouse, Mickey

      1. Mickey.Mouse@WWW.com

      Jack Smyth

      Dduck@WWW.com

       

       

      The File used for the name search looks like this:

       

      Employee Name
      Jack Sprat
      Olive Oil
      Daisy Duck
      Daisy Maye
      Jack Sprat
      Minnie Mouse
      Michael Mouse
      Superman
      Batman

       

       

      Thank you in advance,

      Carol

        • Re: Querying for name spelled multiple ways in 3 columns
          Stefan Wühl

          You can look into a mapping approach, as described e.g. here

          Data Cleansing

           

           

          You would need to create a table with two columns, first column showing all possible ways to name an employee, second column the employee's official name or id, like you want it shown consistently in the data model.

           

          You can then load this table as mapping table:

           

          MAP:

          MAPPING LOAD

          DifferentNamesField, ConsistenNameField

          FROM ..;

           

          Then when loading your tables, use ApplyMap():

           

          LOAD

               Description1,

               ApplyMap('MAP', Description1, 'No Mapping found') as NewDescription1,

               ...

          FROM ...;