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

[anindya_manna]

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'

[carg1]

Hi, 

maybe simply add space

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

[qlikerB96 · Accepted solution]

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.

[jonathandienst]

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,

[tresesco · Accepted solution]

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)

[Anismohamed32]

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

[anindya_manna · Accepted solution]

=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])