topic Find and Replace in QlikView

Find and Replace

Tue, 14 Jun 2011 07:08:07 GMT

Good Morning to all,

I have a stright table in that I have a field name called as "Comments", it contains around 300 rows of values.

My requirement is I would like to find and replace a particular value.

example In the field REMAINDER is a word and I would like to change into CANCEL. Is it possible to replace the values for the entire table.

Please advise..

Joe

Find and Replace

Tue, 14 Jun 2011 07:13:05 GMT

Simply you can use if statement to convert the text

Find and Replace

pat_agen — Tue, 14 Jun 2011 07:16:52 GMT

hi,

check out the Replace() function. this shoudl do what you want.

from help:

Replace ( s, fromstring ,tostring )

Returns a string after replacing all occurences of a given substring within the string s with another substring. The function is non-recursive and works from left to right.

s is the original string.

fromstring is a string which may occur one or more times within string.

tostring is the string which will replace all occurences of fromstring within string.

Example:

replace('abccde','cc','xyz') returns ‘abxyzde’

You can either use it in an expression in your chart or to transform the field when you load it into your qvw from your data source.

Find and Replace

Tue, 14 Jun 2011 07:17:40 GMT

Thanks for your reply,

I tried using If statement, but I failed, Can you expain it....

Regards

Joe

Find and Replace

Tue, 14 Jun 2011 07:20:08 GMT

Did you try like this?

if(Comments='REMAINDER', 'CANCEL',Comments)

Find and Replace

Tue, 14 Jun 2011 07:40:29 GMT

Dear Bala,

Thanks for your update

I have attached my qvw file in the orginal post, can you see and advise me..

Many thanks

Joe

Re: Find and Replace

Tue, 14 Jun 2011 07:55:40 GMT

Sorry I didn't see the attachment before...You can use =replace(Comments,'REMINDER','CANCEL') in the list box

Re: Find and Replace

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 07:58:53 GMT

Hello Joe,

Agree with Pat. Try replacing

 if([Comments]='REMINDER','XXXXXXXXXXXXXX',[Comments]) as comm

In your script by

Replace(Comments, 'REMINDER' 'CANCEL') AS comm

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Find and Replace

Tue, 14 Jun 2011 11:00:48 GMT

Thanks a lot both Bala and Miguel its working fine now...

but using the same replace command I would like to replace multiple values, is it possible..

REPLACE([commentFollow],'n'', 'Name','qu'','Name1') as NcommentFollow

Is it possible..

Thanks a lot again.

Joe

Re: Find and Replace

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 11:07:03 GMT

Hello Joe,

If I understood you right, it is possible, although the function is different. Take a look at the following sample code

CharMap:
MAPPING LOAD * INLINE [
Old, New
á, &aacute;
é, &eacute;
];
Data:
LOAD *,
     MapSubString('CharMap', OriginalField) AS ModifiedField
INLINE [
OriginalField
áé
ae
aé
];

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Find and Replace

Tue, 14 Jun 2011 11:30:58 GMT

Dear Miguel,

thanks a lot....

Joe