topic Find text within a text in QlikView

Find text within a text

Tue, 31 May 2011 08:33:38 GMT

Hi All,

I need help finding text within a textfield.

This is how it look like in the table;

Inside Sales Germany

Inside Sales Netherlands

Integration Sales Germany

Key Account Manager Germany

Key Account Manager Netherlands

Key Account Manager Norway

Manager Inside Sales Netherlands

Manager Inside Sales Norway

Manager Key Account Netherlands

Partner Manager Germany

Senior Key Account Manager

Team Leader Inside Sales Germany

I want to find all "Inside Sales" and put it in a separate table. Should I use subfield then?

Thanks ahead!

Re: Find text within a text

nagaiank — Tue, 31 May 2011 08:45:18 GMT

You may use the 'index' function.

An example is attached. Hope this helps.

Find text within a text

pat_agen — Tue, 31 May 2011 08:50:21 GMT

hi,

index() will do the job.

something lik below (untested)

newTable:

load *

from resident originalTable

where index(originalFieldHere,'Inside Sales')>0;

Find text within a text

Tue, 31 May 2011 09:14:50 GMT

Hi,

What if I want to save this as a new variable, how can I do that?

So this is what I have in mind:

Table1

sales_person

group1 (This column contains only Key Account and Inside Sales)

Table 2

sales_person

group2 (This column contains e.g. Inside Sales Germany and Key Account Manager Netherlands)

Table 1 and Table 2 have different sales_person but what I want to create is one new table, Table 3 containing all sales_person and all group (but only with Key Account and Inside Sales)

Tell me if you dont understand what I mean and I will try to explain it better

Thanks ahead

Find text within a text

pat_agen — Tue, 31 May 2011 09:22:01 GMT

hi Fredrik,

no I am afraid this isn't very clear.

What information do you have to start with? - describe tables, fields and values. And what table do want to achieve? again describe fileds and values in those fields.

Better still - and this is always helpful - load a small qvw with some sample data. Makes everything clearer and will get you answers more rapidly

Find text within a text

Tue, 31 May 2011 09:46:17 GMT

Hi,

Okey I will try again

Table_1

sales_person1 = [1,2,3]

group1 = ["Key Account", "Inside Sales", "Inside Sales"]

Table_2

sales_person2 = [4,5,6]

group2 = ["Inside Sales Germany", "Manager Inside Sales Netherlands", "Senior Key Account Manager"]

The result of these 2 tables should be like below

Table_3

sales_person = [1,2,3,4,5,6]

group = ["Key Account", "Inside Sales", "Inside Sales", "Inside Sales", "Inside Sales", "Key Account"]

So the question is how I in Table 2 can get out only the name "Inside Sales" and "Key Account"

Find text within a text

pat_agen — Tue, 31 May 2011 10:02:20 GMT

hi,

I see your issue like this:

you want everything from table 1. You want added to this records from table 2 where the group includes the strings "Inside Sales" or "key Account". When geting the records from table 2 you want to replace the existing group value with either "Inside Sales" or "key Account".

this can be coded as follows - (we consider that you have already loaded table1 and table 2):

Table_3:

load

sales_person1 as sales_person,

group1 as [group]

resident table_1;

Table_3:

concatenate (Table_3) load

sales_person2 as sales_person,

if(index(group2,'Inside Sales')>0,'Inside Sales',

if(index(group2,'Key Account')>0,'Key Account' )) as [group]

resident table_2

where index(group2,'Inside Sales')>0 or

index(group2,'Key Account')>0;

see if this works.