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
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 |
Hi
Load
Full Name,
subfield(full Name,' ',1) as First Name,
subfield(full Name,' ',2) as Second Name
Tyanks
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 |
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=;
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
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
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
You need to do like below?
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
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