topic Re: Creating a matching database when scripting for later use in QlikView

Creating a matching database when scripting for later use

Tue, 06 Sep 2016 05:58:20 GMT

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
nikeshoe*
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
nikeshoe*	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)';

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

Re: Creating a matching database when scripting for later use

Anonymous — Tue, 06 Sep 2016 15:25:47 GMT

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)';

Re: Creating a matching database when scripting for later use

Peter_Cammaert — Tue, 06 Sep 2016 15:48:52 GMT

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;

Re: Creating a matching database when scripting for later use

cwolf — Tue, 06 Sep 2016 15:52:18 GMT

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

Re: Creating a matching database when scripting for later use

maxgro — Tue, 06 Sep 2016 16:05:21 GMT

another option could be (PFA)

LOAD Keyword as K, rowno() as IdK

FROM

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

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

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

FROM

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

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

NoConcatenate LOAD K as K Resident K;

join (T) LOAD S Resident S;

NoConcatenate load

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

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

Resident T

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

DROP Table T;

Re: Creating a matching database when scripting for later use

Thu, 08 Sep 2016 10:51:40 GMT

Thanks to all for the quick and very qualified replies 🙂

Now my script works perfectly!