topic Re: Wildmatch cell content between two tables in script in QlikView

Wildmatch cell content between two tables in script

Sat, 10 May 2014 21:02:13 GMT

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

Re: Wildmatch cell content between two tables in script

Sat, 10 May 2014 21:51:06 GMT

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

Re: Wildmatch cell content between two tables in script

Sun, 11 May 2014 14:27:01 GMT

Hi Darek,

this works perfectly! Thanks a lot.

Cheers

Matthias