Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tracycrown
Creator III
Creator III

Split Full Name into Sub-Names

Dear all

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

 

Full NameName1Name2Code1Code2
Johny Tan Kim SengJohnyTan7235
Ang Soon TengAngSoon2263

Thank you

Tracy

1 Solution

Accepted Solutions
MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_284434_Pic1.JPG

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

View solution in original post

21 Replies
tresesco
MVP
MVP

Like this?

Load

       *,

       RowNo() as Code,

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

Inline [

"Full Name"

Johny Tan Kim Seng

Ang Soon Teng

]

Capture.JPG

tracycrown
Creator III
Creator III
Author

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.

 

LetterCode
A1
B2
C3
D4
E5
F6
G7
H8
I9
J10
K11
L12
M13
N14
O15
P16
Q17
R18
S19
T20
U21
V22
W23
X24
Y25
Z26

Thank you

Tracy 

Anil_Babu_Samineni

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 ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tresesco
MVP
MVP

This?

Capture.JPG

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

Anonymous
Not applicable

Tresesco,

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

tnx

tresesco
MVP
MVP

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;

Anonymous
Not applicable

Tresesco ..

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

tresesco
MVP
MVP

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.

Anonymous
Not applicable

tnx a lot...