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: 
garethct89
Contributor
Contributor

Repeated Replace

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

Labels (9)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

2 Replies
Saravanan_Desingh

commQV02.PNGAre 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

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;