Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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
22-Imran-13SRMCivil
14,Essa-15CresentB:Arch
15:Anurag,18SathyabamaMech
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 university

MBA

vengadeshpalaniyoganantha321lawranceyoganantha321stalwar1

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

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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

];

Capture.PNG

View solution in original post

4 Replies
maxgro
MVP
MVP

Why Imran has an ID of 13 and not 22?

22-Imran-13SRMCivil
14,Essa-15CresentB:Arch
15:Anurag,18SathyabamaMech
maxgro
MVP
MVP

1.jpg



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)

el_aprendiz111
Specialist
Specialist

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;

vishsaggi
Champion III
Champion III

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

];

Capture.PNG