Search for specific strings

[johnca]

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

[miguelbraga]

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

[miguelbraga]

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

[johnca]

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

[kaushiknsolanki]

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

[miguelbraga]

There are more than one delimiter in your case?

[johnca]

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...

[miguelbraga · Accepted solution]

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

[miguelbraga]

My solution was inspired by this post:

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

[johnca]

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