topic How to use Sub-field Function for Multiple Delimiters and Multiple Numbers? in QlikView

How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

Wed, 25 Nov 2020 16:16:04 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:

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

Re: How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

maxgro — Sat, 06 May 2017 15:49:34 GMT

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

Re: How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

maxgro — Sat, 06 May 2017 15:50:53 GMT

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)

Re: How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

el_aprendiz111 — Sat, 06 May 2017 16:33:19 GMT

Hi,

Re: How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

vishsaggi — Sat, 06 May 2017 16:49:10 GMT

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

];

Re: How to use Sub-field Function for Multiple Delimiters and Multiple Numbers?

KWF_JKassing — Wed, 05 Mar 2025 13:43:40 GMT

Thank you so much!