Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

String Matching using a Separate Table

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

1 Solution

Accepted Solutions
Highlighted
Master III
Master III

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

View solution in original post

11 Replies
Highlighted

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

Highlighted
Contributor II
Contributor II

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

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted

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

Preparing examples for Upload - Reduction and Data Scrambling

Highlighted
Contributor II
Contributor II

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.

Highlighted
Contributor II
Contributor II

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.

Highlighted
Master III
Master III

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

View solution in original post

Highlighted
Contributor II
Contributor II

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.

Highlighted
Contributor II
Contributor II

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