Unusual wiildcard-style query

[alexis]

I have a requirement that I hope someone can assist me with.

I have been asked to "recreate" a SQL Server report in QlikView which, in the main, is very straightforward in terms of the data joins.

The only aspect that baffles me relates to a particular field (let's call it RefNo) which is a 10-digit number. The SQL SELECT that we are replacing is of the order:

SELECT fieldlist.....

FROM tableslist

WHERE

substr(RefNo,1,4) IN ('8418', '8423', '8424')

OR 

substr(RefNo,1,5) IN ('85711')

OR

substr(RefNo,1,6) IN ('630110', '841451', '841460')

OR

substr(RefNo,1,7) IN ('84414869')

OR

substr(RefNo,1,8) IN ('84615011', '85101000')

OR

substr(RefNo,1,9) IN ('850980000')

OR 

substr(RefNo,1,10) IN ('8465950000')

Any ideas as to how I can incorporate this kind of requirement in QlikView - I was even thinking of saving all the combinations in a Bookmark but that would involve a ridiculous amount of manual work and also would not allow for additions or modifications to the codes in future.

Any ideas would be very welcome.

Alexis

[swuehl]

Should be something like

WHERE

match(mid(RefNo,1,4),'8418', '8423', '8424')

OR

match(mid(RefNo,1,5),'85711')

OR

match(mid(RefNo,1,6),'630110', '841451', '841460')

OR

match(mid(RefNo,1,7),'84414869')

OR

match(mid(RefNo,1,8),'84615011', '85101000')

OR

match(mid(RefNo,1,9),'850980000')

OR

match(mid(RefNo,1,10),'8465950000');

(instead of mid(), you could probably also use left() function, without the second argument)

[alexis]

The example posted was an illustration... there are dozens of RefNo codes - once I have pulled the entire data set, I cannot think of an efficient way of selecting the records that are relevant to this "report" that i will generate on the fly based on dozens upon dozens of codes wihout affected the complete data set in my QV document - in other words I will NOT filter this data at the ETL stage ... hope this makes sense. The solution needs to be flexible enough to add or subtract one or more RefNo codes that will come and go....

[swuehl]

Not really getting what you are trying to achieve, but try

wildmatch(RefNo,'8418*', '8423*', '8424*','85711*','630110*', '841451*', '841460*','84414869*','84615011*', '85101000*','850980000*','8465950000*')

[alexis]

Now we are getting closer!

I could store all the refnos in an excel spreadsheet and then create a "wildmatch" statement such as the one listed ...