topic Re: Repeated Replace in QlikView

Repeated Replace

garethct89 — Sat, 16 Nov 2024 19:03:19 GMT

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

Re: Repeated Replace

Saravanan_Desingh — Tue, 21 Apr 2020 02:29:48 GMT

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;

Re: Repeated Replace

Saravanan_Desingh — Tue, 21 Apr 2020 13:14:06 GMT

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;