Extract substring components from a parent string field

[SumitSingh]

Hi,

I have a below requirement. 

I want to seprate molecule list and its corresponding Int-Strength and seperator is  '!' and '+'

Molecule List	Int-Product	NFC123	NFC12	Int-Strength
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+150MG
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+300MG
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	25MG+300MG

tried using Subfiled function but its doesn't work

Expected output :

Molecule List	New Molecule List	Int-Product	NFC123	NFC12	Int-Strength	New Int-Strength
ABC!CDE	ABC	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+150MG	12.5MG
ABC!CDE	CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+150MG	150MG
ABC!CDE	ABC	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+300MG	12.5MG
ABC!CDE	CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+300MG	300MG
ABC!CDE	ABC	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	25MG+300MG	25MG
ABC!CDE	CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	25MG+300MG	300MG

[ggijben · Accepted solution]

Hi SumitSingh,

Can you try the following script:

Data:
LOAD
[Molecule List],
SubField([Molecule List],'!', IterNo()) AS [New Molecule List],
[Int-Product],
NFC123,
NFC12,
[Int-Strength],
SubField([Int-Strength],'+',IterNo()) AS [New Int-Strength]

INLINE [
Molecule List		,Int-Product					,NFC123					,NFC12					,Int-Strength
ABC!CDE				,ABC							,ABC CDE-CTD TABS		,ABC COATED TABLET		,12.5MG+150MG
ABC!CDE				,ABC							,ABC CDE-CTD TABS		,ABC COATED TABLET		,12.5MG+300MG
ABC!CDE				,ABC							,ABC CDE-CTD TABS		,ABC COATED TABLET		,25MG+300MG
]
While IterNo() <= 1+Len(KeepChar([Molecule List],'!'));

It gives the following output:

[SumitSingh · Accepted solution]

Hi @ggijben ,

Thanks for your solution its worked for the above scenarion but i have one more case where field Int-Strength has single string value and give below output.

Data: 

Molecule List	Int-Product	NFC123	NFC12	Int-Strength
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+150MG
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	12.5MG+300MG
ABC!CDE	ABC	ABC CDE-CTD TABS	ABC COATED TABLET	25MG+300MG
EFG!HIJ!KLM	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR
EFG!HIJ!KLM	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR
EFG!HIJ!KLM	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR

output I'm getting :

Molecule List	New Molecule List	Int-Product	NFC123	NFC12	Int-Strength	New Int-Strength
EFG!HIJ!KLM	EFG	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	COMI STR
EFG!HIJ!KLM	HIJ	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	Null
EFG!HIJ!KLM	KLM	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	Null

Expected Output:

Molecule List	New Molecule List	Int-Product	NFC123	NFC12	Int-Strength	New Int-Strength
EFG!HIJ!KLM	EFG	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	COMI STR
EFG!HIJ!KLM	HIJ	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	COMI STR
EFG!HIJ!KLM	KLM	CDF	ABC ABA-CTD TABS	ABA COATED TABLET	COMI STR	COMI STR

Can you please help in this case ?

[ggijben]

Hi @SumitSingh , 

Can you try the following script for the  [New Int-Strength] field?

IF( LEN(KeepChar([Int-Strength],'+'))>0, SubField([Int-Strength],'+',IterNo()), [Int-Strength]) AS [New Int-Strength]

[SumitSingh]

Thanks it worked