Wildmatch cell content between two tables in script

[#1]

Hello,

let's say I have these two simplified tables:

Table1:                             

Request
sushi dortmund
pizza münchen
burger hannover
darmstadt chinese
thai köln
münchen kebap

Table2:

City
münchen
berlin
hamburg
darmstadt
bremen
augsburg
dortmund

I would like to create a table object as a result that shows an extra column that shows for each entry in "Request" if one of the entries in "City" wildmatches. The result should look similar to this (it is kind of a wildmatch VLOOKUP):

Request	CityMatch
sushi dortmund	dortmund
pizza münchen	münchen
burger hannover	-
darmstadt chinese	darmstadt
thai köln	-
münchen kebap	münchen

My solution was just to load both tables and create a chart table object with the dimensions

1. Request

2. =Pick(WildMatch(Request,'*'&city&'*'),city)

This works for the small example tables.

The problem is now that my real data is much bigger. I have around 80.000 rows in "Request" and 1.000 rows in "City". Using the WildMatch-Formula directly in the Table object leads to out of memory errors.

Is there a way of implementing this wildmatch into the script so that I get the CityMatch column already from the script and it does not have to calculate it in the object? Or is there a much better solution for this? I do not really know where to start.

Thanks for any hints

Matthias

[#2 · Accepted solution]

Hello,

you may join tables during relaoad and the check condition, like this:

req:

load *, RowNo() as Req_ID;

LOAD * INLINE [

    Request

    sushi dortmund

    pizza münchen

    burger hannover

    darmstadt chinese

    thai köln

    münchen kebap

];

city:

load *, RowNo() as City_ID;

LOAD * INLINE [

City

münchen

berlin

hamburg

darmstadt

bremen

augsburg

dortmund

];

tab1:

load *;

load City as City1, City_ID, 1 as tech Resident city;

join

load Request as Req1, Req_ID, 1 as tech Resident req;

tab2:

load City_ID, Req_ID, if (SubStringCount(Req1,City1)>0,1) as matches Resident tab1;

inter:

load City_ID, Req_ID Resident tab2 Where matches=1 ;

drop tables tab1, tab2;

regards

Darek

[#3]

Hi Darek,

this works perfectly! Thanks a lot.

Cheers

Matthias