topic Re: How to find string value from Space separated String field in QlikView

How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 17:23:07 GMT

I have field with space separated multiple strings.I want to find a particular string value from that field.The position of that particular string value can be anywhere and it changes.How to find the particlar string?

thanks in advance

Re: How to find string value from Space separated String field

swuehl — Thu, 11 Jun 2015 17:28:03 GMT

What qualifies this special string value? Could you post some sample strings?

Re: How to find string value from Space separated String field

b_garside — Thu, 11 Jun 2015 17:29:38 GMT

There are a number of powerful String functions at your disposal. SubField() , Index() starting position of the n:th occurrence of sub-string, TextBetween() as well. You will most likely need to Nest a couple of these functions.

If you provide some samples of these strings Im sure we can help give you some good starting points.

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 17:34:30 GMT

String field example

Data Source

SQL DB2 Oracle

DB2 SQL Oracle

Excel Oracle SQL

I want to create group based on source type.

Re: How to find string value from Space separated String field

swuehl — Thu, 11 Jun 2015 17:39:24 GMT

And you want to transform these field values into what resulting table / field?

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 17:44:13 GMT

I tried something like this

if(Index([Data Source],'SQL'),'SQL'),'SQL',

if(Index([Data Source],'DB2'),'DB2'),'DB2'

if(Index([Data Source],'Excel'),'Excel'),'Excel','Other') as Data Source Type

For example All string field values with SQL are not grouped as SQL,some values getting dropped??

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 17:47:13 GMT

May be i wrongly interpreted the example field .this is how the field is

itemno Datasource

1 SQL ,DB2 ,Oracle

2 DB2, SQL ,Oracle

3. Excel , Oracle ,SQL

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 17:53:17 GMT

Each data item has Datasource labels/tags (Ex ,excel,sql,oracle).i want to group data items based on data source tags/labels.Sometimes each data item can share same or more than one tag.

Re: How to find string value from Space separated String field

swuehl — Thu, 11 Jun 2015 17:53:39 GMT

Try something like this:

MAP:

MAPPING LOAD F1, F1 as F2 INLINE [

SQL

DB2

Oracle

Excel

];

LOAD itemno, [Data Source], applymap('MAP',trim(subfield([Data Source],' ')),'Other') as Group INLINE [

itemno, Data Source

1, "SQL ,DB2 ,Oracle"

2, "DB2, SQL, Oracle"

3, "Excel, Oracle ,SQL"

]

Re: How to find string value from Space separated String field

rwunderlich — Thu, 11 Jun 2015 17:55:23 GMT

Try:

subfield(String,' ') as Tag

-Rob

Re: How to find string value from Space separated String field

b_garside — Thu, 11 Jun 2015 17:57:51 GMT

Since the Datsource appears to be Tag like in nature with multiple values per row are you trying to pick just one or into separate parts using SubField() etc... ?

Re: How to find string value from Space separated String field

petter — Thu, 11 Jun 2015 17:58:22 GMT

If( DataSource Like '*SQL*' , DataSource , 'Other)

Re: How to find string value from Space separated String field

petter — Thu, 11 Jun 2015 18:02:59 GMT

If( DataSource Like '*SQL*' , 'SQL' , 'Other' )

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 18:06:40 GMT

See below

I want to create a field of source type.If i make selction SQL in source type field i want ot see only SQL items with 1,2,3 and if oracle selected only oracle items 1,3,4

Re: How to find string value from Space separated String field

chaper — Thu, 11 Jun 2015 18:09:02 GMT

I tried .sub field is creating individual values of source and ends up as one many

Re: How to find string value from Space separated String field

b_garside — Thu, 11 Jun 2015 18:11:33 GMT

Perhaps if you could give us a sample mock-up of how it should appear once processed?

I think either SubField or eh Like will do the trick. You will have many combinations of groups since you are allowing multiple Tags per DataSource is that ok?

Re: How to find string value from Space separated String field

b_garside — Thu, 11 Jun 2015 18:15:07 GMT

Then using Subfield will work.

Re: How to find string value from Space separated String field

chaper — Fri, 12 Jun 2015 16:33:06 GMT

Thanks Rob for reply.But doing subfield(String,' ') is creating multiple values for each Item ID.One to many.

Re: How to find string value from Space separated String field

chaper — Fri, 12 Jun 2015 16:34:04 GMT

subfield(String,' ') is creating multiple values for each Item ID.One to many.

Re: How to find string value from Space separated String field

chaper — Fri, 12 Jun 2015 16:34:50 GMT

Thanks for the reply.I will try this code