Creating a matching database when scripting for later use

[#1]

Hello everybody,

I have the following problem, in which you can help me maybe.

I have a database that looks like this:

Keyword	...
nike
nike shoes
nike air max
adidas
adidas shoes
...

And a second database, which looks like this:

Search Query	...
*nike*
*nike*shoe*
*adidas*
...

To maintain a link between the two databases I need a matching database, which should look like this:

Search Query	Keyword
*nike*	nike
*nike*	nike shoes
*nike*	nike air max
*nike*shoe*	nike shoes
*adidas*	adidas
*adidas*	adidas shoes
...

Now I tried the following attempts in QlikView script:

Table B:

Load [Search Query]

from

(OOXML, embedded labels, table is Table 2);

Table A:

Load Keyword

FROM

(OOXML, embedded labels, table is Table 1);

LET vZeilenanzahl = NoOfRows ( 'TableB');

FOR i = 0 to $ (vZeilenanzahl) -1

LET search_value = peek ( '[Search Query]', $ (i), 'TableB');

Data:

LOAD

Keyword

Resident TableA

where the Keyword like '$ (search_value)';

Next

And so I get at least a database "data", which provides me the right part, the Keywords, of the matching database correctly. Unfortunately I do not get the Search queries in the Table.

Do you know a solution for this?

Possibly is my whole approach incorrect and / or there is a much easier way 🙂

Thank you for your help and best regards,

Hagen

[Anonymous · Accepted solution]

try to store the search value in your table Data as well

LET vZeilenanzahl = NoOfRows ( 'TableB');

FOR i = 0 to $ (vZeilenanzahl) -1

LET search_value = peek ( '[Search Query]', $ (i), 'TableB');

Data:

LOAD

'$ (search_value)' as Search_Value,

Keyword

Resident TableA

where the Keyword like '$ (search_value)';

Next

[Peter_Cammaert]

A way uglier solution than Rudolf's (depending on your source tables, it may require quite a lot of RAM), but it avoids using FOR loops. I don't use them if iI don't need to. They're slow.

TempTable:

Load [Search Query]

from

(OOXML, embedded labels, table is Table 2);

JOIN

Load Keyword

FROM

(OOXML, embedded labels, table is Table 1);

FinalTable:

LOAD *

RESIDENT TempTable

WHERE WildMatch(Keyword, [Search Query]);

DROP Table TempTable;

[cwolf]

Or simply:

[Table A]:

Load Keyword

FROM

(OOXML, embedded labels, table is Table 1);

Outer Join([Table A])

Load [Search Query]

from

(OOXML, embedded labels, table is Table 2);

Data:

NoConcatenate

Load

*

Resident [Table A]

Where WildMatch(Keyword,[Search Query])>0;

Drop Table [Table A];

[maxgro]

another option could be (PFA)

K:

LOAD Keyword as K, rowno() as IdK

FROM

[https://community.qlik.com/thread/231603]

(html, codepage is 1252, embedded labels, table is @1);

S:

LOAD [Search Query] as S, rowno() as IdS

FROM

[https://community.qlik.com/thread/231603]

(html, codepage is 1252, embedded labels, table is @2);

T:

NoConcatenate LOAD K as K Resident K;

join (T) LOAD S Resident S;

M:

NoConcatenate load

  *

  //K like '*' & S & '*' as M1,

  //wildmatch(K, '*' & S & '*') as M2

Resident T

Where wildmatch(K, '*' & S & '*');

DROP Table T;

[#6]

Thanks to all for the quick and very qualified replies 🙂

Now my script works perfectly!