topic Wildmatch (or Peek?) over an array in QlikView

Wildmatch (or Peek?) over an array

imark — Wed, 19 Sep 2018 08:52:08 GMT

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:

Beers	TestField
Dead_Tony_Club_5	1
5_PM_Paint_15_01	1
Drunk_Ipa_5	0
Ghibelline_Black_Ale_15_69_78	1
Softcore_Ipa_111	0
...	...

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.

Re: Wildmatch (or Peek?) over an array

marcus_sommer — Wed, 19 Sep 2018 11:16:37 GMT

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

Re: Wildmatch (or Peek?) over an array

imark — Wed, 19 Sep 2018 12:34:10 GMT

Spot on, thanks a lot

Re: Wildmatch (or Peek?) over an array

imark — Wed, 19 Sep 2018 13:05:34 GMT

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?

Re: Wildmatch (or Peek?) over an array

marcus_sommer — Wed, 19 Sep 2018 13:30:27 GMT

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