topic Re: Replace function: Find Multiple strings and replace to blank in QlikView

Replace function: Find Multiple strings and replace to blank

Sun, 15 Feb 2015 07:47:39 GMT

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?

Re: Replace function: Find Multiple strings and replace to blank

maxgro — Sun, 15 Feb 2015 08:33:10 GMT

PFA

Re: Replace function: Find Multiple strings and replace to blank

Colin-Albert — Sun, 15 Feb 2015 11:20:56 GMT

Single characters can be cleaned using keepchar() or purgechar(), just remember these functions are case sensitive.

Replace() can cleanse strings, you can nest several replace commands together.

For example. Replace(replace(replace(yourfieldname, 'abc',''), 'def',''), 'xyz', '') as cleandata

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Sun, 15 Feb 2015 13:23:20 GMT

Excel data:

Script:

map1:

mapping load * inline [

x, y

Robert, ''

Poland, ''

from,'' ] ;

Directory;

LOAD mapsubstring('map1',String)

FROM

[..\..\book2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Outcome:

Re: Replace function: Find Multiple strings and replace to blank

Anonymous — Sun, 15 Feb 2015 15:57:00 GMT

Hi Muralidhar,

Try:

if(match(fieldname,'string_A','string_B','string_C','string_D'),'') as ReplacedString;

if(wildmatch(fieldname,'string*'),'') as ReplacedString;

Regards

Neetha

Re: Replace function: Find Multiple strings and replace to blank

jagan — Mon, 16 Feb 2015 05:33:11 GMT

Hi,

Try MapSubString() and Mapping Load concepts to achieve this. Refer Robert_Mika post for this. This is the best option if you have around 40 values to be replaced.

Regards,

Jagan.

Re: Replace function: Find Multiple strings and replace to blank

maxgro — Mon, 16 Feb 2015 08:05:09 GMT

this is the script if you have personal edition

// make some test string

Source:

load

chr(Ord('a') + rand()*26)

&chr(Ord('a') + rand()*26)

& chr(Ord('A') + rand()*26)

& chr(Ord('A') + rand()*26) as str

AutoGenerate 100;

Concatenate (Source)

load str inline [

str

aaa

bbb

ccc

ddd

eee

fff

];

// string to replace with blank

MapString:

Mapping LOAD * inline [

from, to

aaa,

bbb,

ccc,

ddd,

eee,

fff,

];

Final:

load

// replace with a single command the string in field from of MapString with blank (field to)

ApplyMap('MapString', str) as newstr

Resident

Source;

DROP Table Source;

Re: Replace function: Find Multiple strings and replace to blank

Mon, 16 Feb 2015 17:48:02 GMT

@neetha Your command is replacing whole string to blank. But i would like to replace only particular portion of the string.

Example: hello how are you? (I want to replace hello with blank) and my end result will be... how are you?

Is that make sense? please help.

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Mon, 16 Feb 2015 17:56:05 GMT

Did you check my example?

You can replace multiple strings.

Re: Replace function: Find Multiple strings and replace to blank

Mon, 16 Feb 2015 18:30:02 GMT

@ 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.

Re: Replace function: Find Multiple strings and replace to blank

Mon, 16 Feb 2015 18:57:24 GMT

@ 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.

Re: Replace function: Find Multiple strings and replace to blank

maxgro — Mon, 16 Feb 2015 19:30:02 GMT

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;

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Mon, 16 Feb 2015 20:51:36 GMT

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);

Re: Replace function: Find Multiple strings and replace to blank

Mon, 16 Feb 2015 21:28:34 GMT

@ 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

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Mon, 16 Feb 2015 22:17:00 GMT

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);

Re: Replace function: Find Multiple strings and replace to blank

Tue, 17 Feb 2015 15:17:50 GMT

@ 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!!!

Re: Replace function: Find Multiple strings and replace to blank

Wed, 18 Feb 2015 04:31:47 GMT

@ 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.

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Wed, 18 Feb 2015 08:22:47 GMT

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

Re: Replace function: Find Multiple strings and replace to blank

Wed, 18 Feb 2015 20:22:29 GMT

@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

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

Re: Replace function: Find Multiple strings and replace to blank

robert_mika — Thu, 19 Feb 2015 06:38:20 GMT

Try this:

Directory;

LOAD

Data,

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

FROM

[152744.xlsx]

(ooxml, embedded labels, table is Sheet3);