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

[#1]

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

[maxgro]

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

[maxgro]

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]

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 · Accepted solution]

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

];