topic Remove characters before : in QlikView

Remove characters before :

qlikwiz123 — Mon, 10 Apr 2023 19:21:17 GMT

I have Column - Data that has values in the format below

10/25/2015 : Agent1: This is the right value

05/11/2017 : Agent 56: Please change the report

I need to remove everything before the second ':' and only show the text in my output.

This is the right value

Please change the report

How do I do this in the expression or Script? I tried =Mid(Data, Index(Data, ': ')+2) but this only removes text before the first ':'

Re: Remove characters before :

BrunPierre — Mon, 10 Apr 2023 19:20:56 GMT

Trim(SubField(Field,':',-1))

Re: Remove characters before :

qlikwiz123 — Mon, 10 Apr 2023 19:22:42 GMT

Perfect, thank you!

Re: Remove characters before :

qlikwiz123 — Mon, 10 Apr 2023 19:27:48 GMT

@BrunPierre What if I only want to remove the string before the 1st two ':' and not check for more than two ':'?

Re: Remove characters before :

BrunPierre — Mon, 10 Apr 2023 19:53:07 GMT

And what would that output be?

Re: Remove characters before :

qlikwiz123 — Mon, 10 Apr 2023 19:58:25 GMT

05/11/2017 : Agent 56: Div2: Please change the report

In this case, the column has more than two ':'. The output should be

Div2: Please change the report

Re: Remove characters before :

BrunPierre — Tue, 11 Apr 2023 15:25:51 GMT

This will work in both instances.

TextBetween(Mid(Field,Index(Field,'Agent')),': ','')

Re: Remove characters before :

qlikwiz123 — Tue, 11 Apr 2023 14:12:20 GMT

Sorry. My Bad. Maybe my question wasn't clear.

Original Value:

05/11/2017 : Agent 56: Div2: Please change the report

In this case, the column has more than two ':'. The output should be

Output:

Div2: Please change the report

The output should only eliminate text/characters before the first two ':' occurrences and ignore from the third.

Re: Remove characters before :

BrunPierre — Tue, 11 Apr 2023 14:57:31 GMT

Have you tried this yet?

TextBetween(Mid(Field,Index(Field,'Agent')),': ','')

Re: Remove characters before :

MarcoWedel — Wed, 12 Apr 2023 10:40:58 GMT

Mid(Data, Index(Data, ':', 2)+1)

Re: Remove characters before :

qlikwiz123 — Wed, 12 Apr 2023 13:40:26 GMT

This is more dynamic since I don't have to mention the string 'Agent' as this is not constant. Thank you