topic Replace part of String in QlikView

Replace part of String

Sara1 — Fri, 21 Oct 2022 21:02:57 GMT

Hi,

I'd like to write an expression where if the fourth character of a string is '1', it is replaced with a 'P'. I'm able to replace the character and keep the expression before the fourth character, but am having trouble keeping the latter half of the string because if there is another '1' later in the string, the subfield portion of the expression picks those up as well. How to get the rest of the text after a certain character? I'm sure there are better ways to accomplish what I wrote. Any improvements are also greatly appreciated. Thank you!

This is the expression I used and I'm trying to fill in the blank space. I used the subfield expression below to fill in the space but it is not functioning correctly.

=if(mid([Animal Name], 4,1) = '1', left([Animal Name], 3)&replace(mid([Animal Name], 4,1), '1', 'P'&_______), [Animal Name])

=subfield([Animal Name], mid([Animal Name], 4, 1), -1)

Animal Name	Desired Output
CHI1ABFBW	CHIPABFBW
COWLABFBW	COWLABFBW
PIG12351	PIGP2351
SH11IO1BB	SH1PIO1BB
A111WBI154	A11PWBI154

Re: Replace part of String

Nicole-Smith — Fri, 21 Oct 2022 23:33:17 GMT

I think this should do the trick:
=if(mid([Animal Name], 4, 1) = '1',
left([Animal Name], 3) & 'P' & right([Animal Name], len([Animal Name])-4),
[Animal Name])

Re: Replace part of String

jameskenny11 — Sat, 29 Oct 2022 03:44:12 GMT

old – old substring you want to replace. new – new substring which would replace the old substring. count – (Optional ) the number of times you want to replace the old substring with the new substring.

PrepaidGiftBalance Mastercard

Re: Replace part of String

MarcoWedel — Sat, 22 Oct 2022 10:25:33 GMT

another solution might be:

=If([Animal Name] like '???1*', Left([Animal Name],3)&'P'&Mid([Animal Name],5), [Animal Name])

Re: Replace part of String

sidhiq91 — Sat, 22 Oct 2022 14:11:55 GMT

@Sara1 Please see the code below that I have used in the load Editor:

NoConcatenate
Temp:
Load * inline [
Animal Name
CHI1ABFBW
COWLABFBW
PIG12351
SH11IO1BB
A111WBI154
];

NoConcatenate
Temp1:
Load *,
if(mid([Animal Name],4,1)='1',
left([Animal Name],3)&replace(mid([Animal Name],4,1),'1','P')&right([Animal Name],len([Animal Name])-4),
[Animal Name]) as New_string
Resident Temp;

Drop table Temp;

Exit Script;

IF this resolves your issue, please like and accept it as a solution.