Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

How to replace 2nd occurrence of a sub string from a field

I would like to replace Fund from 'Fundamental XYZ ABC Fund' field.

Replace('Fundamental XYZ ABC Fund' ,'Fund','') gives 'amental XYZ ABC'

But I want 'Fundamental XYZ ABC'

3 Solutions

Accepted Solutions
qlikerB96
New Contributor III

Re: How to replace 2nd occurrence of a sub string from a field

Hi,

It is a very good solution.

There are several string operations which you can use:)

In the script:

Set vText = 'Fundamental XYZ ABC Fund';

Set vCut = 'Fund';

Let vLeft = Left('$(vText)',Len('$(vText)')-Len('$(vCut)')-1);

Let vReplace = Replace('$(vText)',' Fund','');

B.

View solution in original post

MVP
MVP

Re: How to replace 2nd occurrence of a sub string from a field

Use index() to find the 2nd occurrence of your target string to replace and then use mid() to replace and concatenate. Something like:

=Mid(vString,1, Index(vString,'Fund',2)-1)
& Mid(vString, Index(vString,'Fund',2)+5)

Capture.PNG

View solution in original post

Partner
Partner

Re: How to replace 2nd occurrence of a sub string from a field

=if(SubField([Fund Name],' ',SubStringCount([Fund Name],' ')+1)='Fund',left([Fund Name],len([Fund Name]) -len(SubField(Replace([Fund Name],[Fund Platform],''),' ',SubStringCount([Fund Name],' ')+1))),[Fund Name])

View solution in original post

6 Replies
carg1
New Contributor III

Re: How to replace 2nd occurrence of a sub string from a field

Hi, 

maybe simply add space

Replace('Fundamental XYZ ABC Fund' ,' Fund','') 

 

qlikerB96
New Contributor III

Re: How to replace 2nd occurrence of a sub string from a field

Hi,

It is a very good solution.

There are several string operations which you can use:)

In the script:

Set vText = 'Fundamental XYZ ABC Fund';

Set vCut = 'Fund';

Let vLeft = Left('$(vText)',Len('$(vText)')-Len('$(vCut)')-1);

Let vReplace = Replace('$(vText)',' Fund','');

B.

View solution in original post

MVP
MVP

Re: How to replace 2nd occurrence of a sub string from a field

As a script expression:

If(SubStringCount(FundName, 'Fund') = 2,
    Left(FundName, Index(FundName, 'Fund', 2) - 1) & Mid(FundName, Index(FundName, 'Fund', 2) + 4, 9999),
    FundName
) as FundName,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: How to replace 2nd occurrence of a sub string from a field

Use index() to find the 2nd occurrence of your target string to replace and then use mid() to replace and concatenate. Something like:

=Mid(vString,1, Index(vString,'Fund',2)-1)
& Mid(vString, Index(vString,'Fund',2)+5)

Capture.PNG

View solution in original post

Partner
Partner

Re: How to replace 2nd occurrence of a sub string from a field

Hi ,

If your shown example is your problem, then we can go in simple way.

Sol:

Data editor:

load * inline
[a
'Fundamental XYZ ABC Fund'
];
exit Script

KPI :

subfield(a,' ',1)&' '&subfield(a,' ',2)&' '&subfield(a,' ',3)

Hope it helps u

by QlikKiD

 

 

Partner
Partner

Re: How to replace 2nd occurrence of a sub string from a field

=if(SubField([Fund Name],' ',SubStringCount([Fund Name],' ')+1)='Fund',left([Fund Name],len([Fund Name]) -len(SubField(Replace([Fund Name],[Fund Platform],''),' ',SubStringCount([Fund Name],' ')+1))),[Fund Name])

View solution in original post