how to extract the ID in between special characters

[Anonymous]

Hi guys,

could you please help me

Comments

1.Found the root cause of "Reachability bill group issue"& documented it. 2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process AMI Deployment activity*IM001375780& Order life cycle loading issue

From above comments field I need to extract ID number in between  special characters '* 'and '&' .

Expecting Output:

ID	Comments
IM001350816	1.Found the root cause of "Reachability bill group issue"& documented it. 2.Working on CFO data issue
IM001350816	1.Done the CFO data issue incremental load script wise issue 2.Done the aging no read manufacture code mismatch issue. 3.working on ANL enhancement Poc
ALM# 2720	1.Done the data issue incremental load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on ANL enhancement Poc
IM001375780	Support moitoring process AMI Deployment activity Order life cycle loading issue

Regards,

Yuvaraj

[Anil_Babu_Samineni]

May be use this in Script

Load ID, Comments, Concat(Comments, Chr(10), RowNo()) as Comments1 Group By ID;

Load * Inline [

ID, Comments

.....

.....

];

Create Table with ID and Cooments1 as expression

[Anonymous]

could you please give brief explanation i am unable to understand

[Anil_Babu_Samineni]

I assume you have ID and Comments are Fields, You want as expected mentioned above.

Concat is doing by each row with the next line. So here it consumes the data as Cell height.

If i understand wrong can you elaberate more on this

[Anonymous]

Comments is Source table, Second table is Expecting Output table.

[sasiparupudi1]

Use TextBetween

Comments:

load

Comments,

TextBetween(Comments,'*','&') as ID;

Load * Inline

[

Comments

1.Found the root cause of "Reachability bill group issue"& documented it.

2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816&

2.Done the aging no read manufacture code mismatch issue.

3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process

AMI Deployment activity*IM001375780&

Order life cycle loading issue

];

hth

Sas

[Anonymous]

Using Text Between is the option to do as Sasidhar mentioned. Just to add, looks like your requirement needs ID value removed from the Comments So modifying Sasi's answer a bit. Try something like below

Comments:

load

Replace(Comments,Concat('*',TextBetween(Comments,'*','&'),'&'),' ') as Comments,

TextBetween(Comments,'*','&') as ID;

Load * Inline

[

Comments

1.Found the root cause of "Reachability bill group issue"& documented it.

2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue incremental load script wise issue*IM001350816&

2.Done the aging no read manufacture code mismatch issue.

3.working on GAS ANL enhancement Poc *ALM# 2720&

Support monitoring process

AMI Deployment activity*IM001375780&

Order life cycle loading issue

];

Hope it helps.

[Anonymous]

I want to remove the ID from Comments after getting ID as a separate column.

But the replace statement getting error while loading the script.

[Anonymous]

Source table:

Here  the ID will entered two times in second row, In output it need to show as separate, like below table.

Output table:

[Anonymous]

Hi Yuvaraj,

Try with textbetween() function.

=left(TextBetween(Fieldname,'*','&'),12)

find the attachment.

R/Murali.