Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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:
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 ?
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:
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 ?
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]
Thanks it worked