topic Replacing Certain Text in a String Field in QlikView

Replacing Certain Text in a String Field

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

QV12 SR3

I am trying to replace all instances of certain words within each row from an Excel spreadsheet

So within each row where there is a name like this [quote=<name>] i would like to change it to [quote=XXXXXX]

example

[quote=irocfan][quote=Langweilig]

would become

[quote=XXXXXX][quote=XXXXXX]

Example row before

Example row after

I thought i may be able to use this sort of thing but it only changes certain instances for some reason?

Replace([Text of Post],TextBetween([Text of Post],'[quote=',']'),'XXXXXX') as [Text of Post after]

I do NOT want to change it if it specifically says [quote=Sam All] though

The other issue i have is each row is from an Excel cell that i think has line breaks in it (see examples pasted above) so i think it's replacing the line breaks with this _x000D_ sort of thing. Is there any way to keep line breaks applied so when i output to Excel it formats as it was before i loaded the data into qlikview?

I've attached an example Excel file and my qvw

Re: Replacing Certain Text in a String Field

adamdavi3s — Mon, 21 Nov 2016 08:46:35 GMT

Hi Paul,

I've been having a mess around with this for you, the _x000D_ is an HTML line feed after excel has done something weird to it... normally after a csv has been imported to excel (Ansi / non-ansi encoding issues)

Do you have the raw data to play with as you might have more luck importing that, obviously at this time the import is truncating when it hits that line feed.

Anyway when you want to take something out and put it back then I use something like below (e.g. take sam out, replace it with something that won't be in the text, then replace that back at the end:




REPLACE(REPLACE(REPLACE(Replace(REPLACE (REPLACE(REPLACE(
REPLACE([Text of Post],'[quote=Sam All]','|-|-|'),Chr(13),'|C|R|'),Chr(10),'|L|F|'),'_x000D_',''),TextBetween([Text of Post],'[quote=',']'),'XXXXXX'),'|-|-|','[quote=Sam All]'),'|C|R|',Chr(10)),'|L|F|',Chr(10)) as [Text of Post after1]

Re: Replacing Certain Text in a String Field

adamdavi3s — Mon, 21 Nov 2016 08:50:44 GMT

Sorry simpler example for just the sam element

REPLACE(Replace([Text of Post],TextBetween(REPLACE([Text of Post],'[quote=Sam All]','|-|-|'),'[quote=',']'),'XXXXXX'),'|-|-|','[quote=Sam All]')

Re: Replacing Certain Text in a String Field

haymarketpaul — Mon, 21 Nov 2016 11:45:49 GMT

Thanks for reply Adam - still seems to only replace the first instance if [quote= appears more than once in a row.

I achieved it with this in the end - first had to work out how many preceding loads i needed

SubStringCount([Text of Post],'[quote=') as [quote count]

It's only something i need to run at most once a week so i can add or remove as i need to when the time comes based on the quote count

Still doesn't output back to Excel terribly well though

Data:
LOAD
    *
    ,Replace([Text of Post after8],'_x000D_',chr(10))    as [Text of Post after]
    ,RowNo()        as [Row#]
;
LOAD
    *
    ,If(TextBetween([Text of Post after7],'[quote=',']',7)<>'Sam All',
        Replace([Text of Post after7],TextBetween([Text of Post after7],'[quote=',']',7),'XXXXXX'),
            [Text of Post after7]
    ) as [Text of Post after8]
;
LOAD
    *
    ,If(TextBetween([Text of Post after6],'[quote=',']',6)<>'Sam All',
        Replace([Text of Post after6],TextBetween([Text of Post after6],'[quote=',']',6),'XXXXXX'),
            [Text of Post after6]
    ) as [Text of Post after7]
;
LOAD
    *
    ,If(TextBetween([Text of Post after5],'[quote=',']',5)<>'Sam All',
        Replace([Text of Post after5],TextBetween([Text of Post after5],'[quote=',']',5),'XXXXXX'),
            [Text of Post after5]
    ) as [Text of Post after6]
;
LOAD
    *
    ,If(TextBetween([Text of Post after4],'[quote=',']',4)<>'Sam All',
        Replace([Text of Post after4],TextBetween([Text of Post after4],'[quote=',']',4),'XXXXXX'),
            [Text of Post after4]
    ) as [Text of Post after5]
;
LOAD
    *
    ,If(TextBetween([Text of Post after3],'[quote=',']',3)<>'Sam All',
        Replace([Text of Post after3],TextBetween([Text of Post after3],'[quote=',']',3),'XXXXXX'),
            [Text of Post after3]
    ) as [Text of Post after4]
;
LOAD
    *
    ,If(TextBetween([Text of Post after2],'[quote=',']',2)<>'Sam All',
        Replace([Text of Post after2],TextBetween([Text of Post after2],'[quote=',']',2),'XXXXXX'),
            [Text of Post after2]
    ) as [Text of Post after3]    
;
LOAD 
    [User Name]
    ,[Email Address]
    ,Replace([Forum Name],'&amp;','&')        as  [Forum Name]
    ,[Topic Subject]
    ,[Text of Post]        as [Text of Post b4]

    ,If(TextBetween([Text of Post],'[quote=',']',1)<>'Sam All',
        Replace([Text of Post],TextBetween([Text of Post],'[quote=',']',1),'XXXXXX'),
            [Text of Post]
    ) as [Text of Post after2]
    ,[Date and Time]
    ,If(Deleted=0,'FALSE',If(Deleted=1,'TRUE',Deleted))        as Deleted
    ,SubStringCount([Text of Post],'[quote=')        as [quote count]
FROM
Paul.xlsx
(ooxml, embedded labels, table is [SAR Request_2_Mr Pradip Pattni_]);

DROP Fields [Text of Post after2],[Text of Post after3],[Text of Post after4],[Text of Post after5],
    [Text of Post after6],[Text of Post after7],[Text of Post after8]

REPLACE(REPLACE(REPLACE(Replace(REPLACE (REPLACE(REPLACE(	REPLACE([Text of Post],'[quote=Sam All]','\|-\|-\|'),Chr(13),'\|C\|R\|'),Chr(10),'\|L\|F\|'),'_x000D_',''),TextBetween([Text of Post],'[quote=',']'),'XXXXXX'),'\|-\|-\|','[quote=Sam All]'),'\|C\|R\|',Chr(10)),'\|L\|F\|',Chr(10)) as [Text of Post after1]