Discussion Board for collaboration related to QlikView App Development.
Hi all,
I need to get Name as a separate field and I'd as a separate field from Name field by using Sub filed Function.
I have a Data like Below:
Name | College | Department |
10:Muthukumar | Sathyabama | EIE |
11:Ismail | AALi Mohamed Shale | Civil |
12,Prakash | Hindustan | ECE |
22-Imran-13 | SRM | Civil |
14,Essa-15 | Cresent | B:Arch |
15:Anurag,18 | Sathyabama | Mech |
16,Roger | Sathyabama | EIE |
Pratap-17 | Sathyabama | EIE |
18,Kevin | Sathyabama | EIE |
19,palani | Singapore university | MBA |
@From above table, I need to get Name and Id Separately like below table, by using Subfield Function
ID | Name | College | Department |
10 | Muthukumar | Sathyabama | EIE |
11 | Ismail | AALi Mohamed Shale | Civil |
12 | Prakash | Hindustan | ECE |
13 | Imran | SRM | Civil |
14 | Essa | Cresent | B:Arch |
15 | Anurag | Sathyabama | Mech |
16 | Roger | Sathyabama | EIE |
17 | Pratap | Sathyabama | EIE |
18 | Kevin | Sathyabama | EIE |
19 | palani | Singapore university | MBA |
vengadeshpalani yoganantha321 lawrance yoganantha321 stalwar1
I need to implement it in the script. please make use of the below Script
LOAD
Subfield(Name,':',1) as Id,
SubField(Name,':',2) as Name,
College,
Department
FROM
(ooxml, embedded labels, table is Sheet1);
Thanks,
Muthu
Try this may be: not sure if this is a good way but tired.
LOAD SubStringCount(Name, '-') AS HyphenCnt,
Name,
IF(SubStringCount(Name, '-') = 2, Subfield(Name, '-',3),
IF(SubStringCount(Name, '-') = 1 AND SubStringCount(Name, ',') = 1, Subfield(Subfield(Name,',',1),'-',1),
IF(SubStringCount(Name, '-') = 1, Subfield(Name, '-',2),
Subfield(Subfield(Name, ':',1), ',',1)))) AS ID,
//Subfield(Subfield(Subfield(Name, ':',2), ',',1), '-',1) AS Name1,
PurgeChar(Name, '01234567890,:-') AS Name1,
College, Department INLINE [
Name, College, Department
'10:Muthukumar', Sathyabama, EIE
'11:Ismail', AALi Mohamed Shale, Civil
'12,Prakash', Hindustan, ECE
'22-Imran-13', SRM, Civil
'14,Essa-15', Cresent, B:Arch
'15:Anurag,18', Sathyabama, Mech
'16,Roger', Sathyabama, EIE
'Pratap-17', Sathyabama, EIE
'18,Kevin', Sathyabama, EIE
'19,palani', Singapore university, MBA
];
Why Imran has an ID of 13 and not 22?
22-Imran-13 | SRM | Civil |
14,Essa-15 | Cresent | B:Arch |
15:Anurag,18 | Sathyabama | Mech |
tmp:
load
*,
If(
IsNum(Left(New, Index(New, ':') -1)),
Left(New, Index(New, ':') -1),
Right(New, Len(New) - Index(New, ':', -1) -0)
) as Id2 ,
If(
IsNum(Left(New, Index(New, ':') -1)),
SubField(New, ':', 2),
SubField(New, ':', -2)
) as Name2
;
load
*,
Replace(Replace(Name, ',', ':'), '-', ':') as New
;
load * inline [
Name College Department
10:Muthukumar Sathyabama EIE
11:Ismail AALi Mohamed Shale Civil
12,Prakash Hindustan ECE
22-Imran-13 SRM Civil
14,Essa-15 Cresent B:Arch
15:Anurag,18 Sathyabama Mech
16,Roger Sathyabama EIE
Pratap-17 Sathyabama EIE
18,Kevin Sathyabama EIE
19,palani Singapore university MBA
] (delimiter is spaces)
Hi,
LET v_string= '-_><,./?+=)(*&^%$#@!|\`~:0123456789';
TMP:
LOAD *,
PurgeChar(Name,'$(v_string)') AS new_Name,
KeepChar(Name,'0123456789') AS ID;
LOAD * Inline
[
Name| College| Department
10:Muthukumar| Sathyabama| EIE
11:Ismail| AALi Mohamed| Shale Civil
12,Prakash| Hindustan| ECE
22-Imran-13| SRM| Civil
14,Essa-15| Cresent| B:Arch
15:Anurag,18| Sathyabama| Mech
16,Roger| Sathyabama| EIE
Pratap-17| Sathyabama| EIE
18,Kevin| Sathyabama| EIE
19,palani| Singapore university| MBA
](delimiter is '|');
ID:LOAD Min(ID) AS ID Resident TMP;
LET VMin = Peek('ID')-1;
LOAD *,RowNo()+$(VMin) AS new_ID Resident TMP;
DROP Table TMP,ID;
EXIT Script;
Try this may be: not sure if this is a good way but tired.
LOAD SubStringCount(Name, '-') AS HyphenCnt,
Name,
IF(SubStringCount(Name, '-') = 2, Subfield(Name, '-',3),
IF(SubStringCount(Name, '-') = 1 AND SubStringCount(Name, ',') = 1, Subfield(Subfield(Name,',',1),'-',1),
IF(SubStringCount(Name, '-') = 1, Subfield(Name, '-',2),
Subfield(Subfield(Name, ':',1), ',',1)))) AS ID,
//Subfield(Subfield(Subfield(Name, ':',2), ',',1), '-',1) AS Name1,
PurgeChar(Name, '01234567890,:-') AS Name1,
College, Department INLINE [
Name, College, Department
'10:Muthukumar', Sathyabama, EIE
'11:Ismail', AALi Mohamed Shale, Civil
'12,Prakash', Hindustan, ECE
'22-Imran-13', SRM, Civil
'14,Essa-15', Cresent, B:Arch
'15:Anurag,18', Sathyabama, Mech
'16,Roger', Sathyabama, EIE
'Pratap-17', Sathyabama, EIE
'18,Kevin', Sathyabama, EIE
'19,palani', Singapore university, MBA
];