Replace function: Find Multiple strings and replace to blank

[#1]

As a part of cleaning my Big Data text, I have to replace multiple stings to blank (I have almost 40 characters/strings to replace blank).

I am using

LOAD

...

Replace(‘replace_field','string_A',’’) as ReplacedString,

...

FROM ...;

Can I replace all my strings (example: string_B, String_C, String_D etc) to blank in single command?

[#2]

@ Robert

I love to explore this. Can you help me in the scenario if I have more than one field in my data set?

Example:

I have SalesOrder_ID, SalesOrder_BP and SalesOrder_Text  (Here I want to change/replace strings only Sales Order Text field)

Thanks for your help.

[maxgro]

Source:

load str inline [

str

Hello how are you

123aaa123

123bbb123

Say hello

Say Hello

123 Hello Hello 123

];

// string to replace with blank

MapString:

Mapping LOAD * inline [

from, to

aaa,

bbb,

Hello,

];

//

Final:

load

  *,

  MapSubString('MapString', str) as newstr

Resident

  Source;

DROP Table Source;

[robert_mika]

Script:

map1:

mapping load * inline [

x, y

Sales, ''

Order, ''

Text,'' ] ;

Directory;

LOAD mapsubstring('map1',String) as String

FROM

[152744.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#5]

@ Robert sorry I was not explained well in my previous post...

Current Data

SaelsOrder_ID	SalesOrder_BP	SalesOrder_Text
1001	220001	this is Tuesday
1002	220001	Customer is not happy
1003	220001	Customer is happy
1004	220002	very good dinner
1005	220003	I have a quick question
1006	220004	kids are not happy
1007	220005	uncle is not at dinner
Replace	happy'
	dinner'	with BLANK
Desired output
SaelsOrder_ID	SalesOrder_BP	SalesOrder_Text	New Text (Result)
1001	220001	this is Tuesday	this is Tuesday	(no change)
1002	220001	Customer is not happy	Customer is not
1003	220001	Customer is happy	Customer is
1004	220002	very good dinner	very good
1005	220003	I have a quick question	I have a quick question	(no change)
1006	220004	kids are not happy	kids are not
1007	220005	uncle is not at dinner	uncle is not at

[robert_mika · Accepted solution]

map1:

mapping load * inline [

x, y

happy, ''

dinner, ''

] ;

Directory;

LOAD

SaelsOrder_ID,

SalesOrder_BP,

SalesOrder_Text,

mapsubstring('map1',SalesOrder_Text) as Changed

FROM

[152744.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#7]

@ Robert...Thanks you so much!!! This is a wonderful function and we are able to cleanse our data or bring our data from 20+ GBs to 3+ GB. We end up with cleaning lot more strings than we anticipated but this is very handy. Again thank you for all this community help. Even though we purchased Qlik very recent, we feel like we have great support and I feel that we can rock in Qlik!!! 

[#8]

@ Robert One more question...after cleansing my data, I did see lot of white spaces in my text box. Is there a way I can clean that white spaces? (BTW I don't want to clear the white spaces between words)., I did see a similar solution in Qlik Community but I am not sure how I apply to this. Remove blank spaces between a string

Thanks for your help.

[robert_mika]

You can use TRIM,LTRIm or RTRIM but if you provide an example that would help to give you more accurate answer.

[#10]

@Robert After I ran above cleanse program, I got below sample file. But I would like get rid of empty spaces. is it possible?

Sample in the file:

///////////////////////////////////////////////////////////

    c c  1c

'02'00. '01

'02'01. '01

'02'02. '01

//////////////////////////////////////////////////////////////

My desired result is below...

////////////////////////////////////////////////////////////

c c  1c

'02'00. '01

'02'01. '01

'02'02. '01

////////////////////////////////////////////////////////////////

[robert_mika]

Try this:

Directory;

LOAD

Data,

if(len(Trim(Data))>0 ,Trim(Data))

FROM

[152744.xlsx]

(ooxml, embedded labels, table is Sheet3);