Wildmatch (or Peek?) over an array

[imark]

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.

[marcus_sommer · Accepted solution]

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

[imark]

Spot on, thanks a lot

[imark]

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]

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