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

[sunny_talwar]

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

[Anonymous]

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

[jonathandienst]

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.

[sunny_talwar]

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

Preparing examples for Upload - Reduction and Data Scrambling

[Anonymous]

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.

[Anonymous]

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.

[antoniotiman · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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