Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

"Does not contain" this string in the field value

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.

6 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

This is the answer that worked for me

Anonymous
Not applicable
Author

Very nice