Split Full Name into Sub-Names

[tracycrown]

Dear all

Kindly help to advise how to split full name into sub-names and assign code (or number) to all sub-names.

Full Name	Name1	Name2	Code1	Code2
Johny Tan Kim Seng	Johny	Tan	72	35
Ang Soon Teng	Ang	Soon	22	63

Thank you

Tracy

[cweiping]

I guess it is Name split each letter and conver to code

.Ex Johyn is (10+15+8+14+25 ) = 72

LETTER	CODE
J	10
O	15
H	8
N	14
Y	25
Johny	72

[Akina0929]

Hi

Load

    Full Name,

subfield(full Name,' ',1) as First Name,

subfield(full Name,' ',2) as Second Name

Tyanks

[tracycrown]

Dear Tresesco

The Code can be any numbers below (1 to 26 or 1 to 9) and not necessary must be ASCII nos. depending on users' requirements.

Thank you

Letter	Code	Code
A	1	1
B	2	2
C	3	3
D	4	4
E	5	5
F	6	6
G	7	7
H	8	8
I	9	9
J	10	1
K	11	2
L	12	3
M	13	4
N	14	5
O	15	6
P	16	7
Q	17	8
R	18	9
S	19	1
T	20	2
U	21	3
V	22	4
W	23	5
X	24	6
Y	25	7
Z	26	8

[cweiping]

Dear Tracy

this is my solution,maybe you can reference.

Load Script

Formula_tp1:

    NoConcatenate

    LOAD

        *,

        mid([Full Name],FindOneOf([Full Name],' ',IterNo()-1)+1,FindOneOf([Full Name],' ',IterNo())-(FindOneOf([Full Name],' ',IterNo()-1)+1)) as Char_NM

        ,FindOneOf([Full Name],' ',IterNo()-1)+1 as Char_Star

        ,FindOneOf([Full Name],' ',IterNo())-(FindOneOf([Full Name],' ',IterNo()-1)+1) as Char_Len

        ,IterNo() as No

    While IterNo() <= SubStringCount([Full Name],' ');

    load *

    Resident Data_Name ;

    Formula_tp2:

    NoConcatenate

    LOAD

    *

    ,

    upper(Mid(Char_NM,IterNo(),1)) as Letter

    Resident Formula_tp1 while iterno()<=len(Char_NM);

    left join(Formula_tp2)

    load Letter,Code Resident MD_Code;

    Formula_tp3:

    NoConcatenate

    load

    [Full Name]

    ,No

    ,Char_NM

    ,sum(Code) as Char_Sum

    Resident Formula_tp2 group by [Full Name],Char_NM,No;

    Generic

    Char_NM:

load  [Full Name],'Name_'& No ,Char_NM

Resident Formula_tp3;

Set vTP_ListOfTables = ;

    For vTP_TableNo = 0 to NoOfTables()

    Let vTP_GenTableName = TableName($(vTP_TableNo)) ;

      If Subfield(vTP_GenTableName,'.',1)='Char_NM' Then

        Let vTP_ListOfTables = vTP_ListOfTables & If(Len(vTP_ListOfTables)>0,',') & Chr(39) & vTP_GenTableName & Chr(39) ;

    End If

    Next vTP_TableNo

    For each vTP_GenTableName in $(vTP_ListOfTables)

      Left Join (Data_Name) Load * Resident [$(vTP_GenTableName)];

      Drop Table [$(vTP_GenTableName)];

    Next vTP_GenTableName

    Generic

    Char_Num:

load  [Full Name],'Code_'& No ,Char_Sum

Resident Formula_tp3;

Set vTP_ListOfTables = ;

    For vTP_TableNo = 0 to NoOfTables()

    Let vTP_GenTableName = TableName($(vTP_TableNo)) ;

      If Subfield(vTP_GenTableName,'.',1)='Char_Num' Then

        Let vTP_ListOfTables = vTP_ListOfTables & If(Len(vTP_ListOfTables)>0,',') & Chr(39) & vTP_GenTableName & Chr(39) ;

    End If

    Next vTP_TableNo

    For each vTP_GenTableName in $(vTP_ListOfTables)

      Left Join (Data_Name) Load * Resident [$(vTP_GenTableName)];

      Drop Table [$(vTP_GenTableName)];

    Next vTP_GenTableName

    //drop table Data_Name;

    drop table Formula_tp1;

    drop table Formula_tp2;

    drop table Formula_tp3;

    drop table MD_Code;

    set vTP_GenTableName=;

    set vTP_ListOfTables=;

    set vTP_TableNo=;

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be:

mapCode:
Mapping LOAD Letter, Code&'+'
FROM [https://community.qlik.com/servlet/JiveServlet/download/1397473-306837/Test-1.xlsx] (ooxml, embedded labels, table is Code);

tabNames:
LOAD *,
    If(Len(Name1),Evaluate(MapSubString('mapCode',Upper(Name1))&'0')) as Code1,
    If(Len(Name2),Evaluate(MapSubString('mapCode',Upper(Name2))&'0')) as Code2,
    If(Len(Name3),Evaluate(MapSubString('mapCode',Upper(Name3))&'0')) as Code3,
    If(Len(Name4),Evaluate(MapSubString('mapCode',Upper(Name4))&'0')) as Code4;
LOAD [Full Name],
    SubField([Full Name],' ',1) as Name1,
    SubField([Full Name],' ',2) as Name2,
    SubField([Full Name],' ',3) as Name3,
    SubField([Full Name],' ',4) as Name4
FROM [https://community.qlik.com/servlet/JiveServlet/download/1397473-306837/Test-1.xlsx] (ooxml, embedded labels, table is Data)
Where Len([Full Name]);

hope this helps

regards

Marco

[tracycrown]

Dear Mr Macro Wedel

Wow, you have provided me a simple and an easy to understand solution. Many thanks for your kind advice.

Aside to Mr Weiping Chung

Your solution is complicated and I find it difficult to understand.

Thank you

Tracy

[tracycrown]

Dear Mr Macro Wedel

Can you advise how to sum all Codes to get a Total in the script ?.

I have tried to insert a line (Code1+Code2+Code3+Code4) but it does not work.

Thank you

Tracy

[Anil_Babu_Samineni]

You need to do like below?

1. mapCode: 
2. Mapping LOAD Letter, Code&'+' 
3. FROM [https://community.qlik.com/servlet/JiveServlet/download/1397473-306837/Test-1.xlsx] (ooxml, embedded labels, table is Code); 
4.  
5. tabNames: 
6. Load *, Code1+Code2+Code3+Code4 as TOTAL
   
7. LOAD *, 
8.     If(Len(Name1),Evaluate(MapSubString('mapCode',Upper(Name1))&'0')) as Code1, 
9.     If(Len(Name2),Evaluate(MapSubString('mapCode',Upper(Name2))&'0')) as Code2, 
10.     If(Len(Name3),Evaluate(MapSubString('mapCode',Upper(Name3))&'0')) as Code3, 
11.     If(Len(Name4),Evaluate(MapSubString('mapCode',Upper(Name4))&'0')) as Code4; 
12. LOAD [Full Name], 
13.     SubField([Full Name],' ',1) as Name1, 
14.     SubField([Full Name],' ',2) as Name2, 
15.     SubField([Full Name],' ',3) as Name3, 
16.     SubField([Full Name],' ',4) as Name4 
17. FROM [https://community.qlik.com/servlet/JiveServlet/download/1397473-306837/Test-1.xlsx] (ooxml, embedded labels, table is Data) 
18. Where Len([Full Name]); 

[tracycrown]

Dear Anil Babu

Thank you so much for your quick response. Please help to check my mistake as I still cannot get the correct answer.

Thank you

Tracy

[tracycrown]

Dear Anil Babu

Sorry, I forgot to attach script in my previous response. Please refer to attached script for your kind review.

Thank You

Tracy