String Matching using a Separate Table

[Anonymous]

Hi All,

In my Qlikview load script I have a section of code that looks something like:

if(STRING_COL LIKE '*PATTERN_1A*' or STRING_COL LIKE '*PATTERN_1B*', 'SEGMENT_1',

if(STRING_COL LIKE '*PATTERN_2A*' or STRING_COL LIKE '*PATTERN_2B* or

   STRING_COL LIKE '*PATTERN_2C*, 'SEGMENT_2', 'OTHERS'))

What I'd like to do is to be able to load in an excel or CSV table into Qlikview containing two columns: Pattern and Segment. My thoughts are then that I would be able to split the Pattern_Segment table into subsets for each segment and then do something like:

if(STRING_COL contains '*SUBSET_1.Pattern*', 'SEGMENT_1',

if(STRING_COL contains '*SUBSET_2.Pattern*', 'SEGMENT_2', 'OTHERS'))

There are a fixed number of segments but the number of patterns in each segment may vary hence the need for a table of patterns and segments.

Kind Regards,

Lorcan

[Anonymous]

Hi Antonio,

Can I ask another question. Your solution answers the question I asked but I was wondering if you could help me with an enhancing attempt of the solution.

Is it possible to adapt your solution so that if a string pattern can be matched using an earlier term in the mapping table then the segment is filled. For example, using an adjusted version of your code, the first term in the string column: "Text and Text and PATTERN_2A and More Text PATTERN_1A" can be matched with "SEGMENT2" or "SEGMENT1" due to the "PATTERN_2A" and "PATTERN_1A" patterns existing in the string. I would like to prioritise the order of the mapping table, so in this instance, the segment I would like to assign for this string would be "SEGMENT1" as this would be the first match in the mapping table. At the moment, the segment being assigned to this string is "SEGMENT2" and "PATTERN_2A" occurs before "PATTERN_1A".

MapTable_Key:
Mapping LOAD Pattern,'{<'&Seg&'>}' as Seg Inline [
Pattern,Seg
PATTERN_1A,SEGMENT1
PATTERN_1B,SEGMENT1
PATTERN_2A,SEGMENT2
];

TAB:
LOAD *,
if(len(TextBetween(MapSubString('MapTable_Key',Field),'{<','>}')) = 0, 'OTHERS',TextBetween(MapSubString('MapTable_Key',Field),'{<','>}')) as Segment Inline [
Field
Text and Text and PATTERN_2A and More Text PATTERN_1A
Text and Text and fPATTERN_1B and More Text
Text and Text and and More Text PATTERN_2A
Something Else];

[antoniotiman]

I'm sorry,No.

Mapping LOAD admits only 2 Fields.

Regards,

Antonio