topic Re: Dynamic Wildmatch expression in QlikView

Dynamic Wildmatch expression

alexis — Tue, 02 Jul 2013 10:25:59 GMT

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

Re: Dynamic Wildmatch expression

kaushiknsolanki — Tue, 02 Jul 2013 10:50:10 GMT

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

Re: Dynamic Wildmatch expression

alexis — Tue, 02 Jul 2013 11:42:24 GMT

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

Re: Dynamic Wildmatch expression

stevegimbrollmt — Tue, 26 Nov 2013 16:44:24 GMT

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

Re: Dynamic Wildmatch expression

jonathjl — Wed, 27 Nov 2013 03:16:41 GMT

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

Re: Dynamic Wildmatch expression

alexis — Thu, 28 Nov 2013 18:41:51 GMT

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

Re: Dynamic Wildmatch expression

stevegimbrollmt — Sat, 07 Dec 2013 15:41:17 GMT

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