Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Extract pre-defined keywords out of string

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

1 Solution

Accepted Solutions
sunny_talwar

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

];


Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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?

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

];


Capture.PNG

twanqlik
Creator
Creator
Author

Thanks!