Dynamic Wildmatch expression

[alexis]

Hi

In the below expression (highlighted in yelow) the components ('8418*', '8423*' etc) will come from an Excel file - there are over 100 of them although I am just showing 4 here for clarity. As this list of numbers is likely to change from time to time this is the only sensible way that I could come up with to incorporate this.

The idea is that I will display all records that have a nin-zero "GP97Match" value..

Two questions:

a) Does anyone know how I can build this dynamic list, and/or

b) Are there better ways of doing this - i.e. perhaps store the list of numbers in a QlikView table and link it somehow with my data?

Thanx in advance

Alexis

[kaushiknsolanki]

Hi,

     One possible way can be something like below.

     1. Load the table from excel where the search criteria is stored.

     2. Concatenate all the values with the comma and *.

     Example script is

Search:
Load Value&'*' as Value inline [
Value
a
b
c
d
e
f
g
h
];

Concat:
Load  Replace(chr(34)& Concat(Value,',')&chr(34),',','","') as Search
Resident Search;

Let vSearch = Peek('Search',0,'Concat');

Data:
Load WildMatch(ABC,$(vSearch)) as XYZ
From XYZ

Regards,

Kaushik Solanki

[alexis]

Helpful answer - the delimiter however is single quotes chr(39) and not double quotes chr(34).

I got muddled up trying to create a variable that would give us the desired string:

The following did not work - can you assist?

LET test = chr(39) & chr(39) & chr(44) & chr(39) & chr(39);

[stevegimbrollmt]

Did you manage to solve this? I have a similar situation.. thanks!

[jonathjl]

T_Patterns:

LOAD

    Patterns

FROM data.xls

(biff, embedded labels, table is [Patterns$]);

// Patterns has two entries Matt and Mark

T_Temp:

Load  Replace(chr(39)& Concat('*' & Patterns & '*',',') & chr(39),   ',' ,  Chr(39) & ',' & chr(39)) as Search

Resident T_Patterns;

Let vSearch = Peek('Search',0,'T_Temp');

// vSearch is now '*Matt*','*Mark*'

// Load only people whose name has a Matt or Mark somewhere in the name

T_People:

Load * where MatchFlag > 0;

LOAD

    Wildmatch(Fullname, $(vSearch)) as MatchFlag,

     Fullname,

     City

FROM data.xls

(biff, embedded labels, table is [People$]);

[alexis]

Hi Steve,

In the end I used some of the suggestions made and the following is my code straight from the application:

CodeList:

LOAD concat(chr(39) & A & '*' & chr(39), ',') As ValueList

FROM

CommodityCodes.xlsx

(ooxml, no labels, table is Sheet1);

Let vSearch = Peek('ValueList',0,'CodeList');

and used vSearch as follows:

LOAD *,

  wildmatch(CommodityCode,$(vSearch)) As Match;

Hope this helps

Alexis

[stevegimbrollmt]

Thanks for the help, both answered were valuable and work.