topic Re: how to use sub field function for multiple delimiters? in QlikView

how to use sub field function for multiple delimiters?

Thu, 04 May 2017 09:57:37 GMT

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:

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

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

tresB — Thu, 04 May 2017 10:12:24 GMT

Try like:

Load

Keepchar(Name, '0123456789') as Id,

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

yoganantha321 — Thu, 04 May 2017 11:04:45 GMT

Hi muthu,

it working great as per the Tresesco code

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

Fri, 05 May 2017 07:20:30 GMT

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

output for above data:

Thanks,

Muthu