Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Spot on, thanks a lot
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?
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