Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

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
Highlighted
Specialist III
Specialist III

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;
Highlighted
Specialist III
Specialist III

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