Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Creating a matching database when scripting for later use

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 QueryKeyword
*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

1 Solution

Accepted Solutions
hrlinder
Honored Contributor

Re: Creating a matching database when scripting for later use

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

5 Replies
hrlinder
Honored Contributor

Re: Creating a matching database when scripting for later use

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

Re: Creating a matching database when scripting for later use

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;

chrwolf64
Contributor III

Re: Creating a matching database when scripting for later use

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];

MVP
MVP

Re: Creating a matching database when scripting for later use

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;

Not applicable

Re: Creating a matching database when scripting for later use

Thanks to all for the quick and very qualified replies :-)

Now my script works perfectly!

Community Browser