topic Re: Split Full Name into Sub-Names in QlikView

Split Full Name into Sub-Names

tracycrown — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Split Full Name into Sub-Names

tresB — Fri, 08 Dec 2017 05:16:56 GMT

Like this?

Load

RowNo() as Code,

SubField("Full Name", ' ') as SplitedNames

Inline [

"Full Name"

Johny Tan Kim Seng

Ang Soon Teng

]

Re: Split Full Name into Sub-Names

tracycrown — Fri, 08 Dec 2017 05:28:33 GMT

Dear Tresesco B

Your suggestion is not correct as each character has been assigned a code below. The correct answer is also included in the worksheet.

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

Thank you

Tracy

Re: Split Full Name into Sub-Names

Anil_Babu_Samineni — Fri, 08 Dec 2017 05:54:02 GMT

I haven't seen any values over excel where you are getting Codes in answer sheet? Does it mean use case of Ang is the Alphabet of mapping numeric such as 1+14+7 = 22 for Ang in Code1 ??

Re: Split Full Name into Sub-Names

tresB — Fri, 08 Dec 2017 05:59:31 GMT

This?

Or, you are looking for exactly like in excel Code1, Code2..?

Re: Split Full Name into Sub-Names

Anonymous — Fri, 08 Dec 2017 06:31:45 GMT

Tresesco,

could you pls solution to the one who have posted..

tnx

Re: Split Full Name into Sub-Names

tresB — Fri, 08 Dec 2017 06:38:55 GMT

temp:

Load *,

SubField("Full Name", ' ') as SplitedNames

Inline [

"Full Name"

Johny Tan Kim Seng

Ang Soon Teng

];

Final:

Load

       [Full Name],

       Sum(AsciiCode) as Code,

       SplitedNames

Group By [Full Name], SplitedNames;

Load

       [Full Name],

       SplitedNames,

       Mid(SplitedNames,IterNo(),1) as Letter,

       Ord(Lower(Mid(SplitedNames,IterNo(),1)))-96 as AsciiCode

Resident temp while iterno()<=len(SplitedNames);

Drop Table temp;

Re: Split Full Name into Sub-Names

Anonymous — Fri, 08 Dec 2017 06:56:07 GMT

Tresesco ..

Ord(Lower(Mid(SplitedNames,IterNo(),1)))-96 // cud u pls help me in understanding this line especially 96

Re: Split Full Name into Sub-Names

tresB — Fri, 08 Dec 2017 07:04:08 GMT

John,

ASCII (output of ord function ) for 'a','b', ... are like 97,98..

I could use a mapping to get 1 for a, 2 for b... but I use this method to extract the letter position using ASCII sequence. Subtracting 96 from the ascii nos, gives me the right positions.

Re: Split Full Name into Sub-Names

Anonymous — Fri, 08 Dec 2017 07:14:23 GMT

tnx a lot...

Re: Split Full Name into Sub-Names

cweiping — Fri, 08 Dec 2017 07:49:08 GMT

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

Re: Split Full Name into Sub-Names

Akina0929 — Fri, 08 Dec 2017 09:48:13 GMT

Load

Full Name,

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

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

Tyanks

Re: Split Full Name into Sub-Names

tracycrown — Sat, 09 Dec 2017 02:14:09 GMT

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

Re: Split Full Name into Sub-Names

cweiping — Sat, 09 Dec 2017 13:54:42 GMT

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

Re: Split Full Name into Sub-Names

MarcoWedel — Sun, 10 Dec 2017 18:53:51 GMT

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

Re: Split Full Name into Sub-Names

tracycrown — Mon, 11 Dec 2017 04:06:53 GMT

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

Re: Split Full Name into Sub-Names

tracycrown — Sat, 16 Dec 2017 03:41:30 GMT

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

Re: Split Full Name into Sub-Names

Anil_Babu_Samineni — Sat, 16 Dec 2017 04:06:32 GMT

You need to do like below?

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 *, Code1+Code2+Code3+Code4 as TOTAL
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]);

Re: Split Full Name into Sub-Names

tracycrown — Sat, 16 Dec 2017 13:18:55 GMT

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

Re: Split Full Name into Sub-Names

tracycrown — Sat, 16 Dec 2017 13:29:56 GMT

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