topic replacing text in a string in QlikView

replacing text in a string

hopkinsc — Tue, 19 Jan 2016 12:01:07 GMT

Hi all, i have a field with values such as..

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

etc

i want to separate the string into its own value, i.e.

Admin

Audit

Marketing

Sales

then store these new values into a field.

i have used the following..

if(Wildmatch(MY_FIELD,'*Sales*')>0,'Sales',

if(wildmatch(MY_FIELD,'IT^')>0,'IT')) as Dept;

this works for all of the values apart from 'IT' as IT is also present in 'Audit'.

does anyone know of a way i can separate IT and not include Audit?

Re: replacing text in a string

sunny_talwar — Tue, 19 Jan 2016 12:03:34 GMT

Try this may be:

SubField(MY_FIELD, ', ') as MY_FIELD_NEW

Re: replacing text in a string

marcus_sommer — Tue, 19 Jan 2016 12:04:48 GMT

Use match() instead of wildmatch().

- Marcus

Re: replacing text in a string

hopkinsc — Tue, 19 Jan 2016 12:10:21 GMT

Subfield doesnt work, there could be multiple values in a string, some might just be 2 values, others might have 6.

Re: replacing text in a string

mightyqlikers — Tue, 19 Jan 2016 12:11:37 GMT

use subfield(field_name,',') as newfield

Regards

S@M

Re: replacing text in a string

Chanty4u — Tue, 19 Jan 2016 12:12:23 GMT

chk dis thread

Re: replacing text in a string

Chanty4u — Tue, 19 Jan 2016 12:12:30 GMT

sry Replace function: Find Multiple strings and replace to blank

Re: replacing text in a string

hopkinsc — Tue, 19 Jan 2016 12:13:43 GMT

Match only seems to pick up the IT line, it doesnt search other strings for IT (Sales, Marketing, IT).

what i want is to be able to select IT in the new field and that have all values in the original field with IT in the string to become a 'white' value

Re: replacing text in a string

sunny_talwar — Tue, 19 Jan 2016 12:14:11 GMT

Doesn't matter how many values you have. If you do it in script it will create a seperate value for each of them. Will create a sample to show you

Re: replacing text in a string

Anonymous — Tue, 19 Jan 2016 12:14:45 GMT

may be like this?

if( MY_FIELD like '*Sales*','Sales',

if(MY_FIELDlike 'IT*','IT')) as Dept;

Re: replacing text in a string

sunny_talwar — Tue, 19 Jan 2016 12:17:12 GMT

Script:

Table:

LOAD *,

SubField(MY_FIELD, ', ') as MY_FIELD_NEW;

LOAD * Inline [

MY_FIELD

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

] (delimiter is '|');

Output:

Re: replacing text in a string

Chanty4u — Tue, 19 Jan 2016 12:18:09 GMT

Load
SubField(NewString,'#',1) as MainCategory,
SubField(NewString,'#',2) as NewCategory;
Load SubField(String,',') as NewString;
Load * Inline [
String
"

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

"
]

Re: replacing text in a string

hopkinsc — Tue, 19 Jan 2016 12:21:15 GMT

thats great, the LIKE function worked.

thanks very much

Re: replacing text in a string

Chanty4u — Tue, 19 Jan 2016 12:37:27 GMT

yes ur ri8 sunny...