Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to extract the ID in between special characters

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:

  

IDComments
IM0013508161.Found the root cause of "Reachability bill group issue"& documented it.
2.Working on CFO data issue
IM0013508161.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# 27201.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
IM001375780Support moitoring process
AMI Deployment activity
Order life cycle loading issue

Regards,

Yuvaraj

1 Solution

Accepted Solutions
avinashelite

Try like this

LOAD ID,

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

Where len(trim(SubField(ID,'_')))>0;

LOAD SubField(ID,'_') as ID,

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

;

LOAD

TextBetween(Comments,'*','&')&'_'&TextBetween(Comments,'*','&',2)&'_'&TextBetween(Comments,'*','&',3)&'_'&TextBetween(Comments,'*','&',4) as ID,

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

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

];

View solution in original post

16 Replies
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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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

sasiparupudi1
Master III
Master III

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

Source table:

Comments.png

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

Output table:

Commets2.png

Anonymous
Not applicable
Author

Hi Yuvaraj,

Try with textbetween() function.

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

find the attachment.




R/Murali.