Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Wild card search in a loading script

Hello,

Let say I have a table with Brands:

Brand

YAMAHA

SELMER

....

I have an other table with product name which include the brand name

ProductName

Drums YAMAHA xxx

Saxophone Alto SELMER

SELMER Saxophone barython

I would like to create a link between these two table using a wild card search of the Brand into the ProductName.

Is there any function I can use in the script to achieve this.

Thanks

Philippe

1 Solution

Accepted Solutions
prieper
Master II
Master II

Think that WILDMATCH-function should help you a bit:

// ======================== Load a list of Brandnames
Brand:
LOAD
*
INLINE [Brand
YAMAHA
SELMER];
// ========================== Load a list of Products
Products:
LOAD * INLINE [
ProductName
Drums YAMAHA xxx
Saxophone Alto SELMER
SELMER Saxophone barython];
// ======== Loop through Products checking for Brands
FOR i = 1 TO NoOfRows('Brand')
LET sBrand = PEEK('Brand', i-1, 'Brand');
Product_n_Brand:
LOAD
ProductName,
'$(sBrand)' AS Brand
RESIDENT
Products
WHERE
WILDMATCH(ProductName, '*$(sBrand)*');
NEXT i
DROP TABLE Products;


HTH
Peter

View solution in original post

5 Replies
prieper
Master II
Master II

Think that WILDMATCH-function should help you a bit:

// ======================== Load a list of Brandnames
Brand:
LOAD
*
INLINE [Brand
YAMAHA
SELMER];
// ========================== Load a list of Products
Products:
LOAD * INLINE [
ProductName
Drums YAMAHA xxx
Saxophone Alto SELMER
SELMER Saxophone barython];
// ======== Loop through Products checking for Brands
FOR i = 1 TO NoOfRows('Brand')
LET sBrand = PEEK('Brand', i-1, 'Brand');
Product_n_Brand:
LOAD
ProductName,
'$(sBrand)' AS Brand
RESIDENT
Products
WHERE
WILDMATCH(ProductName, '*$(sBrand)*');
NEXT i
DROP TABLE Products;


HTH
Peter

Not applicable
Author

Thanks a lot ....

That helps.

Philippe

amien
Specialist
Specialist

What if i add a brandname : SELM.

Then i will have 2 matches. How can i only get one match? first found match

Great example btw

amien
Specialist
Specialist

and what if i want to put the non matches in an 'other' Brandname?

nathanfurby
Specialist
Specialist

Oops.. sorry, didn't mean to suggest that last post as an answer.

FYI - using the NOT keyword before the wildmatch works for me when wanting to exclude matches i.e.

NOT WILDMATCH(ProductName, '*$(sBrand)*');