topic Re: String Matching using a Separate Table in QlikView

String Matching using a Separate Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: String Matching using a Separate Table

sunny_talwar — Tue, 15 Aug 2017 11:16:07 GMT

May be look at this. This isn't directly related, but it is sort of what you are looking to do...

Data Cleansing

If you still have trouble, I would suggest sharing some sample data and expected output for us to help you better

Re: String Matching using a Separate Table

Anonymous — Tue, 15 Aug 2017 13:08:30 GMT

Hi Sunny,

I don't think that'll work. I'd need something like wildmatch for the string contains part and the list of possibilities (U.K., Great Britain, etc.) to come from a excel table.

As for sample data and output the systems in here have stopped me uploading data before but I'll see if I can write up a toy example on this.

Kind Regards,

Lorcan

Re: String Matching using a Separate Table

jonathandienst — Tue, 15 Aug 2017 13:15:17 GMT

How many patterns do you need? If it's in the hundreds (or less), you could construct the WildMatch() expressions in the load script before applying them.

I don't know the maximum length of an expression, but this may not work if you have thousands of patterns, of course.

Re: String Matching using a Separate Table

sunny_talwar — Tue, 15 Aug 2017 13:16:02 GMT

All I am looking for some mocked up data... or may be scrambled data

Preparing examples for Upload - Reduction and Data Scrambling

Re: String Matching using a Separate Table

Anonymous — Tue, 15 Aug 2017 14:39:13 GMT

There are 60 patterns in total with 14 segments. I have the patterns hard-coded in the load script at the moment but the idea is that this list maybe updated in the future with new patterns within the segments.

Re: String Matching using a Separate Table

Anonymous — Tue, 15 Aug 2017 14:56:30 GMT

Hi Sunny.

I understand. I used to ask a load of questions on stackoverflow and a requirement there was to upload some sample data and your best effort code-wise so far. When I tried before to upload a mocked up toy sample onto this before I got a "suspected data leak" message from the company's IT systems which prevented me from uploading a toy sample. I might give an example using the comments section so I'll try that soon.

Re: String Matching using a Separate Table

antoniotiman — Tue, 15 Aug 2017 15:03:15 GMT

Hi Lorcan,

try like this

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

LOAD *,TextBetween(MapSubString('MapTable',Field),'{<','>}') as Segment Inline [
Field
Text and Text and PATTERN_1Aand More Text
Text and Text and PATTERN_1B and More Text
Text and Text and PATTERN_2A and More Text];

Regards,

Antonio

Re: String Matching using a Separate Table

Anonymous — Tue, 15 Aug 2017 15:13:43 GMT

Hi Antonio, thank your response. Just so I'm sure and that I wont misapply your solution in my own code, would you be able to tell me briefly what the steps are doing above. This will help me in my learning of Qlikview.

Re: String Matching using a Separate Table

Anonymous — Tue, 15 Aug 2017 15:20:04 GMT

Actually on second reflection I think I understand. I'll apply this to my code and will give you the credit when it works.

Re: String Matching using a Separate Table

Anonymous — Fri, 25 Aug 2017 08:42:43 GMT

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];

Re: String Matching using a Separate Table

antoniotiman — Fri, 25 Aug 2017 09:01:28 GMT

I'm sorry,No.

Mapping LOAD admits only 2 Fields.

Regards,

Antonio