topic Search for specific strings in QlikView

Search for specific strings

johnca — Tue, 13 Sep 2016 14:47:24 GMT

I need help determining if some specific strings are present in a field and return a yes if it is. I don't need them parsed out. The data looks like below with the expected inclusion or not. Numbers appear to be stored as text.

Rules are:

Numbers begin with 551 or 651 and 10 digits long.

Numbers begin with 751 or 851 or 951 and 8 digits long.

Value	Include?
01/01/15 - 01/16/15	no
01/27/16 - 02/23/16	no
2 Wall Brackets	no
202 - 649301703 - 001 Period 07/29/16 - 08/28/16	no
342-7278 163-4096 Period 06/01/16 - 07/05/16	no
2016 - 2017 Renewal Fee	no
8186, 8476, 8831	no
1720281 & 1720291	no
75100660/ 75100827	yes
511977911 - 01/12/16	no
4001745021 - 07/08/16	no
5510002648/ 5510006394	yes
5510005214/ 5510005788	yes
5510006967/ 5510007038/ 5510007030	yes
5510007016/5510004208	yes
5510007038/ 5510007678/ 5510007364/ 5510006967/ 5510007174/ 5510007030	yes
8772106120569243	no
Account Number: ABC02699025	no
Acct 202-649301702-001 Period 02/19/16 - 03/18/16	no
Acct 500000882 Period 08/22/15 - 09/21/15	no
Acct 500000882 Period 09/22/15 - 10/21/15	no
Acct 85175552691 10/20/15 - 11/19/15	no
Acct 8310005107651; 01/19/2016	no
Acct 3000317097117834672 Period 06/22/16 - 07/21/16	no
acct# 85175552691 Period 05/22/16 - 06/21/16	no
Client Number 762293	no
Contract# 8000011734	no
Cust# 10638269	no
Customer # 6000607033	no
Kitchen Supplies	no
MANAGEMENT FEE	no
Membership Dues	no
Order Number 9000251488	no
Plastic Cup Tumbler	no
Project 381533800IS	no
Quarterly Maintenance Service Contract	no
REFERRAL FEE	no
Registration Fees	no
Requisition 55900024515	no
75100454	yes
75100660	yes
75100663	yes
85100002	yes
85100029	yes
85100042	yes
5510000354	yes
5510000682	yes
5510001412	yes
5510002529	yes
5510002647	yes
6510000049	yes
6510000054	yes
55100000354	no

Thanks,

John

Re: Search for specific strings

miguelbraga — Tue, 13 Sep 2016 14:57:14 GMT

Hey there,

You can do something like this:

Table:

LOAD *,

if(left(Value, 3) = '551' or left(Value, 3) = '651',

if(len(Value) = 10, 'yes', 'no'), // true for Numbers begin with 551 or 651 and 10 digits long

if(left(Value, 3) = '751' or left(Value, 3) = '851' or left(Value, 3) = '951',

if(len(Value) = 8, 'yes', 'no') // true for Numbers begin with 751 or 851 or 951 and 8 digits long

as Include;

LOAD *

FROM [your database connection goes here];

Re: Search for specific strings

miguelbraga — Tue, 13 Sep 2016 15:00:05 GMT

Or simplify using this:

Table:

LOAD *,

if( (left(Value, 3) = '551' or left(Value, 3) = '651') and len(Value) = 10,

'yes', // true for Numbers begin with 551 or 651 and 10 digits long

if((left(Value, 3) = '751' or left(Value, 3) = '851' or left(Value, 3) = '951') and len(Value) = 8,

'yes', // true for Numbers begin with 751 or 851 or 951 and 8 digits long

'no') as Include;

LOAD *

FROM [your database connection goes here];

Re: Search for specific strings

johnca — Tue, 13 Sep 2016 15:03:30 GMT

Thanks Miguel, you are right and that is similar to what I had, except I forgot some possible values. The desired values I'm seeking could also be preceded by another number or text a la;

Order Number 6519090101

1050 - 75100123

Re: Search for specific strings

kaushiknsolanki — Tue, 13 Sep 2016 15:04:02 GMT

HI,

You can use below script.

Load Value,

if(wildmatch(Left(Value,3),'551','651') and Len(Value) >=10,'Yes',

if(wildmatch(Left(Value,3),'751','851') and Len(Value) >=8,'Yes','No')) as Include

From xyz;

Regards,

Kaushik Solanki

Re: Search for specific strings

miguelbraga — Tue, 13 Sep 2016 15:06:10 GMT

There are more than one delimiter in your case?

Re: Search for specific strings

johnca — Tue, 13 Sep 2016 15:10:55 GMT

Yes. The field is free-form text, so it may be just about anything. So far I've seen only dashes but could be semi-colons, slashes...

Re: Search for specific strings

miguelbraga — Tue, 13 Sep 2016 15:20:30 GMT

Try this:

ReplaceMap:
MAPPING LOAD * INLINE [

char replace
)
)
”
,
/
–
] (delimiter is ‘ ‘)
;

Table:

LOAD *,

if( (left(Value, 3) = '551' or left(Value, 3) = '651') and len(Value) = 10,

'yes', // true for Numbers begin with 551 or 651 and 10 digits long

if((left(Value, 3) = '751' or left(Value, 3) = '851' or left(Value, 3) = '951') and len(Value) = 8,

'yes', // true for Numbers begin with 751 or 851 or 951 and 8 digits long

'no') as Include;

LOAD *,

Subfield(ValueTransformed, ' ', 2) as Value;

LOAD *,
MapSubString(‘ReplaceMap‘, Value) as ValueTransformed;
LOAD *

FROM [your database connection goes here];

Re: Search for specific strings

miguelbraga — Tue, 13 Sep 2016 15:22:13 GMT

My solution was inspired by this post:

http://qlikviewcookbook.com/2009/01/using-mapsubstring-to-edit-strings/

Re: Search for specific strings

johnca — Tue, 13 Sep 2016 17:20:35 GMT

Thanks for the alternate view on this. I will play with this one as well.

Re: Search for specific strings

johnca — Tue, 13 Sep 2016 19:46:05 GMT

Miguel, this indeed does what I asked and even better, what I need. I've really only used ApplyMap and the alternate MapSubString is way more versatile...nice. I added a couple characters to the delimiter map as well. I will certainly use this function and method on some other data. Thank you!

And yes, gotta love Rob, and his QlikView Cook Book!

--john