topic Re: Extract pre-defined keywords out of string in QlikView

Extract pre-defined keywords out of string

twanqlik — Thu, 04 Jan 2018 13:53:41 GMT

I have 2 fields:

1) Strings

2) Search field

I want to mark the strings that contain a name that is listed in Search Field.

The result should be that a third field will be added, with the mark:

1) Strings

1.1) String - Search field.

2) Search field

With this i can easily filter on strings that contain a specific keyword that is stored in 'Search field'.

I'm struggling with this, does someone know how to accomplish this

Example data:

String:

1) This hotel has a very poor service.

2) This hotel has a great service.

3) The festival was awesome

4) The new birthday campaign is launched

Search fields:

poor service

great service

festival

birthday campaign

Result

String: This hotel has a very poor service.

Search field: poor service

String: This hotel has a great service.

Search field: [Blank]

String: The festival was awesome

Search field: festival

String: The new birthday campaign is launched

Search field: birthday campaign

Re: Extract pre-defined keywords out of string

sunny_talwar — Thu, 04 Jan 2018 14:00:04 GMT

May be try this

MappingTable:

Mapping

LOAD Search_String,

'\' & Search_String & '/';

LOAD * INLINE [

Search_String

poor service

great service

festival

birthday campaign

];

Fact:

LOAD *,

TextBetween(MapSubString('MappingTable', Strings), '\', '/') as SearchField;

LOAD * INLINE [

Strings

This hotel has a very poor service.

This hotel has a great service.

The festival was awesome

The new birthday campaign is launched

];

But what happens if you have more than 1 search field in a particular string?

Re: Extract pre-defined keywords out of string

Anil_Babu_Samineni — Thu, 04 Jan 2018 14:01:54 GMT

Ok, But what happens if you have more than 1 search string field in a allocation string?

Re: Extract pre-defined keywords out of string

sunny_talwar — Thu, 04 Jan 2018 14:05:35 GMT

For multiple, this might work

MappingTable:

Mapping

LOAD Search_String,

'\' & Search_String & '/';

LOAD * INLINE [

Search_String

poor service

great service

festival

birthday campaign

];

Fact:

LOAD *,

SubField(SubField(MapSubString('MappingTable', Strings), '\'), '/') as SearchField;

LOAD * INLINE [

Strings

This hotel has a very poor service but awesome festival.

This hotel has a great service.

The festival was awesome

The new birthday campaign is launched

];

Right Join (Fact)

LOAD Search_String as SearchField

INLINE [

Search_String

poor service

great service

festival

birthday campaign

];

Re: Extract pre-defined keywords out of string

twanqlik — Thu, 04 Jan 2018 16:15:20 GMT

Thanks!