Separate text in columns

[jumiprado]

Hey guys i have a table like this

Folder_ID	Client	Other_Clients
33322/18	JHON (ID 1934)	PETER (ID 3465) | ROBERT (ID 5863)
56742/17	ANN (ID 1294)	PETER (ID 3465) | SUSAN (ID 5863) | WILL (ID 7254)

And I Need this resultant table

Folder_ID	CLIENT	ID_CLIENT
33322/18	JHON	ID 1934
33322/18	PETER	ID 3465
33322/18	ROBERT	ID 5863
56742/17	ANN	ID 1294
56742/17	PETER	ID 3465
56742/17	SUSAN	ID 5863
56742/17	WILL	ID 7254

Can you help me with this?

Thanks!

[albert_guito]

Hi,

You can try with

Subfield(Client,'(',1) as CLIENT
Subfield(PurgeChar(Client,')',),'(',2) as ID_CLIENT

Ag+

[miguelbraga]

Hi there,

Refer to this code:

Original:
LOAD * INLINE [
Folder_ID, Client, Other_Clients
33322/18, JHON (ID 1934), PETER (ID 3465) | ROBERT (ID 5863)
56742/17, ANN (ID 1294), PETER (ID 3465) | SUSAN (ID 5863) | WILL (ID 7254)
];

table1:
LOAD
Folder_ID,
Subfield(Clients, ' ', 1) as CLIENT,
Replace(Replace(Subfield(Clients, ' ', 2) & ' ' & Subfield(Clients, ' ', 3), '(', ''), ')', '') as ID_CLIENT
;
LOAD
Folder_ID,
Subfield(Other_Clients,' | ') as Clients
Resident Original;

table2:
Load
Folder_ID,
Subfield(Client, ' ', 1) as CLIENT,
Replace(Replace(Subfield(Client, ' ', 2) & ' ' & Subfield(Client, ' ', 3), '(', ''), ')', '') as ID_CLIENT
Resident Original;

Drop Table Original;

Hope this helps you.

Best Regards,

MB

[sunny_talwar · Accepted solution]

This might work as well

Table:
LOAD Folder_ID,
	 SubField(CLIENT, '(', 1) as CLIENT,
	 TextBetween(CLIENT, '(', ')') as ID_CLIENT;
LOAD Folder_ID,
	 SubField(Client & ' | ' & Other_Clients, ' | ') as CLIENT;
LOAD * INLINE [
    Folder_ID, Client, Other_Clients
    33322/18, JHON (ID 1934), PETER (ID 3465) | ROBERT (ID 5863)
    56742/17, ANN (ID 1294), PETER (ID 3465) | SUSAN (ID 5863) | WILL (ID 7254)
];