Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Querying for name spelled multiple ways in 3 columns

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

1 Reply
MVP
MVP

Re: Querying for name spelled multiple ways in 3 columns

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

Community Browser