Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Like this?
Load
*,
RowNo() as Code,
SubField("Full Name", ' ') as SplitedNames
Inline [
"Full Name"
Johny Tan Kim Seng
Ang Soon Teng
]
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
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 ??
This?
Or, you are looking for exactly like in excel Code1, Code2..?
Tresesco,
could you pls solution to the one who have posted..
tnx
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;
Tresesco ..
Ord(Lower(Mid(SplitedNames,IterNo(),1)))-96 // cud u pls help me in understanding this line especially 96
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.
tnx a lot...