Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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];
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;
Thanks to all for the quick and very qualified replies 🙂
Now my script works perfectly!