Repeated Replace

[garethct89]

Hi

I have a chat string which I am wanting to trim down so that only the customers words are contained. See below.

09:00:56 infoYou are now chatting with Nathan.
09:01:08 Nathan:David: Hi i have sold my car so would like to cancel my breakdown cover please
09:01:59 Nathan: Hello David. Not a problem. For security can you please confirm your full name and vehicle registration?
09:02:16 David: David *** ******
09:03:00 Nathan: Perfect, thank you. What date are you wanting me to cancel from?
09:03:08 David: Today please

Nathan is the agent and David is the customer. So far I have used the function -

'replace(Transcript,Textbetween(Transcript,Name,Visitor),'') as TEST2'

which works but only for the first occurrence, you can see this looking at the timestamp in the above  09:01:08, this previously said '09:01:08 Nathan: Hi you're through to Nathan how can I help?' I have managed to delete out Nathans first sentence in the string but I am wanting to delete out everything Nathan says. Basically I want to repeat this function across all of the string.

Name field is Agent name

Visitor field is Customer name

Transcript field is the long text string that contains the conversation

I've been at this all day so any help will be much appreciated!

Thanks

Gareth

[Saravanan_Desingh]

Are you looking something like this?

ts:
LOAD RowNo() As RowID,* INLINE [
    transcript
    09:00:56 infoYou are now chatting with Nathan.
    09:01:08 Nathan:Hi you're through to Nathan how can I help?
    09:01:08 David: Hi i have sold my car so would like to cancel my breakdown cover please
    09:01:59 Nathan: Hello David. Not a problem. For security can you please confirm your full name and vehicle registration?
    09:02:16 David: David *** ******
    09:03:00 Nathan: Perfect, thank you. What date are you wanting me to cancel from?
    09:03:08 David: Today please
]
(delimiter is '\t');

tabAgent:
LOAD SubField(transcript, 'infoYou are now chatting with ',-1) As AgentStr Resident ts
Where transcript Like '*infoYou are now chatting with *';

Let vAgent = SubField(Peek('AgentStr', 0, 'tabAgent'), '.',1);

Trace *** vAgent=$(vAgent);

tabCust:
First 1
LOAD SubField(Mid(transcript,10,25), ':',1) As CustStr Resident ts
Where Not transcript Like '*infoYou are now chatting with *'
And Not transcript Like '??:??:?? $(vAgent):*'
And transcript Like '??:??:?? *:*'
;

Let vCust = Peek('CustStr', 0, 'tabCust');

Trace *** vCust=$(vCust);

CustTS:
NoConcatenate
LOAD * Resident ts
Where transcript Like '??:??:?? $(vCust):*';

Drop Table ts, tabAgent, tabCust;

[Saravanan_Desingh · Accepted solution]

ts:
LOAD RowNo() As RowID,* INLINE [
    transcript
    09:00:56 infoYou are now chatting with Nathan.
    09:01:08 Nathan:Hi you're through to Nathan how can I help?
    09:01:08 David: Hi i have sold my car so would like to cancel my breakdown cover please
    09:01:59 Nathan: Hello David. Not a problem. For security can you please confirm your full name and vehicle registration?
    09:02:16 David: David *** ******
    09:03:00 Nathan: Perfect, thank you. What date are you wanting me to cancel from?
    09:03:08 David: Today please
]
(delimiter is '\t');

tabAgent:
LOAD TextBetween(transcript, 'infoYou are now chatting with ','.') As AgentStr Resident ts
Where transcript Like '*infoYou are now chatting with *';

Let vAgent = Peek('AgentStr', 0, 'tabAgent');

Trace *** vAgent=$(vAgent);

tabCust:
First 1
LOAD SubField(Mid(transcript,10,25), ':',1) As CustStr Resident ts
Where Not transcript Like '*infoYou are now chatting with *'
And   Not transcript Like '??:??:?? $(vAgent):*'
And 	  transcript Like '??:??:?? *:*'
;

Let vCust = Peek('CustStr', 0, 'tabCust');

Trace *** vCust=$(vCust);

CustTS:
NoConcatenate
LOAD * Resident ts
Where transcript Like '??:??:?? $(vCust):*';

Drop Table ts, tabAgent, tabCust;