Grouping of similar but slightly different name

[upaliwije]

Hi Friends

I have following data in a  table

Name              Amount

Mr. P Perera   1000

P.Perera         2000

p. perera         3000

N.asoka           3000

N Asoka           500

Mr.N.Asoka      1000

Above names denote two persons but in data table entered with slight differences. I want to group the in two rows. How can I do it in a large data table

[Anonymous]

Create you can create a more column in script with Upper(Name) as UpName, and use this column in your chart for grouping

[hic]

I would use Applymap(). See Don't join - use Applymap instead.

The idea is to create a "translation table" where you list all variants of the name in the first column, and what you would like to use instead in the second. Load this using "Mapping Load". Then you can load the data using

  Load

    Applymap('NameTranslation', Name) as Name,

    Amount

    From ...

HIC

[robert_mika]

if you got only two persons

t1:

load * inline

[ name,Amount

Mr. P Perera,1000

P.Perera,2000

p. perera,3000

N.asoka, 3000

N Asoka, 500

Mr.N.Asoka,1000

]

;

final:

load

Amount,

if(right(Upper(name),5)='ASOKA','Mr N.Asoka','Mr.P Perera') as Name

resident t1;

drop table t1

for larger data set

is the surname always preceding with dot or space?

[#5]

Hi

  First Trim the MR from the Field afterwards make all fields to capitalize   it will work

  Trim MR from the Fields by using Qlikview Function  like Index, subfield ....etc

Thanks

Manju