Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to use sub field function for multiple delimiters?

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:

NameCollegeDepartment
10:MuthukumarSathyabamaEIE
11:IsmailAALi Mohamed ShaleCivil
12,PrakashHindustanECE
Imran-13SRMCivil
14,EssaCresentB:Arch
15:AnuragSathyabamaMech
16,RogerSathyabamaEIE
Pratap-17SathyabamaEIE
18,KevinSathyabamaEIE
19,palaniSingapore universityMBA

from above table, I need to get Name and Id Separately like below table, by using Subfield Function


    

IdNameCollegeDepartment
10MuthukumarSathyabamaEIE
11IsmailAALi Mohamed ShaleCivil
12PrakashHindustanECE
13ImranSRMCivil
14EssaCresentB:Arch
15AnuragSathyabamaMech
16RogerSathyabamaEIE
17PratapSathyabamaEIE
18KevinSathyabamaEIE
19palaniSingapore universityMBA

vengadeshpalaniyoganantha321sivabose

I need to implement it in the script. please make use of the below Script

My Script:

LOAD

Subfield(Name,':',1) as Id,

SubField(Name,':',2) as Name,

     College,

     Department

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

Muthu

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

Load 

       Keepchar(Name, '0123456789') as Id,

       Capitalize(Keepchar(lower(Name), 'abcdefghijklmnopqrstuvwxyz')) as Name

View solution in original post

3 Replies
tresesco
MVP
MVP

Try like:

Load 

       Keepchar(Name, '0123456789') as Id,

       Capitalize(Keepchar(lower(Name), 'abcdefghijklmnopqrstuvwxyz')) as Name

yoganantha321
Creator II
Creator II

Hi muthu,

it working great as per the Tresesco code

Screenshot_47.png

Kindly mark it as correct answer so that it will be useful for others

Not applicable
Author

Thanks for your response tresesco‌ and yoganantha321 it is working fine.

but if I use it in another scenario it is taking all the numbers present in the Name field

if i have a data Like this it is not working for it

for below-bolded data it is taking all the numbers but i need only 13,14 is there any way or logic to get it

   

NameCollegeDepartment
10:MuthukumarSathyabamaEIE
11:IsmailAALi Mohamed ShaleCivil
12,PrakashHindustanECE
22-Imran-13SRMCivil
14,Essa-15CresentB:Arch
15:Anurag,18SathyabamaMech
16,RogerSathyabamaEIE
Pratap-17SathyabamaEIE
18,KevinSathyabamaEIE
19,palaniSingapore universityMBA

output for above data:

Screenshot_1.png

Thanks,

Muthu