Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Create you can create a more column in script with Upper(Name) as UpName, and use this column in your chart for grouping
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
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?
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