Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
Did you manage to solve this? I have a similar situation.. thanks!
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$]);
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
Thanks for the help, both answered were valuable and work.