Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
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'

Labels (3)
3 Solutions

Accepted Solutions
qlikerB96
Contributor III
Contributor III

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

tresesco
MVP
MVP

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

anindya_manna
Partner
Partner
Author

=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
Contributor III
Contributor III

Hi, 

maybe simply add space

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

 

qlikerB96
Contributor III
Contributor III

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
Partner
Partner

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
tresesco
MVP
MVP

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

Anismohamed32
Partner
Partner

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
Partner
Partner
Author

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