"Does not contain" this string in the field value

[#1]

Hi All,

I have a field which has 100 different values. I'm writing an if condition where I'm required to select only those field values which does not contain a specific string. Could anyone please tell me what function can we use to attain this?

Example: I have a field called Company_Status. I want to include all those values in this field  which does not contain "Rejected" anywhere in the value.

if( Company_Status does not contain "Rejected" , A, B);

Thanks! Any help would be highly appreciated.

[#2]

Hi,

Use index(s1 , s2[ , n])

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in

string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the

end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Examples:

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

Regards,

Xue Bin

[Miguel_Angel_Baeyens]

Hi,

The If() conditional should look like this. WildMatch allows you to use wildcards in values, and will return 0 if the patterns (you can specify more than one) is not found in the expression (Company_Status field in this case)

If(WildMatch(Company_Status, '*Rejected*') = 0, 'A', 'B') AS NewField

Another option is using the LIKE operator, although this one only allows one matching pattern

=If(NOT Company_Status LIKE '*Rejected*', 'A', 'B')

Hope that helps.

Miguel

[#4]

Hi Miguel,

Thanks for your reply. So, if I'am using the option2 , can you please tell me what am I doiung wrong in this if statement?

Load if(wildmatch(Country,'United States of America','China','Taiwan','Germany (Federal Republic of)', Country ='Netherlands') >0

and wildmatch(Status= 'Allowed','Granted')> 0

and wildmatch(Company,'ABC') >0 and 

wildmatch(DFAnalysis,'Low') >0 AND

IFSCORE >= 3

and NOT Tag LIKE '*Apple-Tabled*', 'LOW', 'NA')  as VettingStage,

  ID

                    Resident Temp;

[Miguel_Angel_Baeyens]

Hi,

Basing my assumption entirely on the syntax, I'd change it to not overuse WildMatch, that is a very slow function, and check some other syntax issues to look like this:

LOAD If(WildMatch(Country,'*United States of America*', '*China*', '*Taiwan*', '*Germany (Federal Republic of)*','*Netherlands*') 
       AND Match(Status, 'Allowed','Granted')
       AND Company = 'ABC' 
       AND DFAnalysis = 'Low'  
       AND IFSCORE >= 3
       AND NOT Tag LIKE '*Apple-Tabled*'
     , 'LOW', 'NA')  AS VettingStage,
     ID
Resident Temp;

WildMatch() is used when you need to use wildcards, Match() is used when you need the SQL IN or CASE functionality (a value that has to be matched to a list of values) and equal, greater than or equal to operators when you only need to check against one value. WildMatch() and Match() will return fasl when they are equal to zero, otherwise the function returns true.

Hope that helps.

Miguel

[#6]

This is the answer that worked for me

[Anonymous]

Very nice