Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
imark
Contributor III
Contributor III

Wildmatch (or Peek?) over an array

Let's set the stage (dummy data)

RandomBrewery:

LOAD * INLINE [

    Beers

    Dead_Tony_Club_5

    5_PM_Paint_15_01

    Drunk_Ipa_5

    Ghibelline_Black_Ale_15_69_78

    Softcore_Ipa_111

    Sing_The_Bismarck_1942

    The_End_Of_Geography_89

    Granny_State_45

];

IDs

LOAD * INLINE [

     Column_1

     Tony_Club

     5_PM

     Ale_15_69_78

];    

Problem:

I need to create a combination of an If() and a WildMatch() statement that compares each row in RandomBrewery against all rows in IDs and returns 1 if there is a match, else 0. Also worth mentioning is that I want to keep one row per original entry in RandomBrewery. Thus the final result should look like:


TargetTable:

   

BeersTestField
Dead_Tony_Club_51
5_PM_Paint_15_011

Drunk_Ipa_5

0
Ghibelline_Black_Ale_15_69_781
Softcore_Ipa_1110
......

As you can see, the data in IDs does not follow a clear pattern, that's why I think there is no path to follow other than WildMatch().

In my attempts so far I tried to pack the column values from IDs into a variable while also adding asterisks at the beginning and end of the string:


TempTable:

LOAD

     '*' & Column_1 & '*'  AS WildColumn

RESIDENT IDs

;


LET myVar1 = Peek(WildColumn)

;


NewTable:

LOAD

     Beers

     ,IF

If(WildMatch(Beers, $(myVar1)),1,0)

RESIDENT RandomBrewery

;


Unfortunately this approach works well only for the first row from TempTable.WildColumn. Or on the second, or third according to how I set the second parameter from Peek() but always one at a time.


I tried to wrap the LET statement around a FOR loop but to no avail.

FOR x=0 to 2

LET myVar1 = Peek(WildColumn)

NEXT;


And by now I am out of ideas. Any help will be much appreciated.

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Such things could be done with a mapping like:

IDs:

mapping LOAD *, chr(1) INLINE [

     Column_1

     Tony_Club

     5_PM

     Ale_15_69_78

];    

RandomBrewery:

load *, len(keepchar(mapsubstring('IDs', Beers), chr(1))) as TestField;

LOAD * INLINE [

    Beers

    Dead_Tony_Club_5

    5_PM_Paint_15_01

    Drunk_Ipa_5

    Ghibelline_Black_Ale_15_69_78

    Softcore_Ipa_111

    Sing_The_Bismarck_1942

    The_End_Of_Geography_89

    Granny_State_45

];

- Marcus

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Such things could be done with a mapping like:

IDs:

mapping LOAD *, chr(1) INLINE [

     Column_1

     Tony_Club

     5_PM

     Ale_15_69_78

];    

RandomBrewery:

load *, len(keepchar(mapsubstring('IDs', Beers), chr(1))) as TestField;

LOAD * INLINE [

    Beers

    Dead_Tony_Club_5

    5_PM_Paint_15_01

    Drunk_Ipa_5

    Ghibelline_Black_Ale_15_69_78

    Softcore_Ipa_111

    Sing_The_Bismarck_1942

    The_End_Of_Geography_89

    Granny_State_45

];

- Marcus

View solution in original post

imark
Contributor III
Contributor III
Author

Spot on, thanks a lot

imark
Contributor III
Contributor III
Author

Just one more thing, could you enlighten me on the role of Chr(1) in this formula?

Edit: wait, let me guess, it could actually be anything, right?

marcus_sommer
MVP & Luminary
MVP & Luminary

Mapsubstring() replaced the lookup-value with the return-value. This alone will be enough if you really want to replace certain chars, for example html special-chars with ascii-chars like here: Re: Passing parameter strings that contain special characters.

But here was the aim to create a flag and therefore I choosed a char which is quite unlikely part of the normal text (this means it couldn't be any char else anyone which doesn't appear in the normal text) and removed with keepchar() all other chars and then len() could be used to check if there is any content in the fieldvalue or not.

- Marcus