community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Need help with CONTAINS function

Asteroid

I'm trying to see if customer search terms match a product name that was later clicked on. I'm using the contains function to see if the product name contains what the customer used as a search term.  For the most part it's working, but one weird thing I keep seeing is that if there is a space then the function isn't counting it as a match. 

 

For example: 

search #search termproduct name match
1nikenike air yes
2nike airnike air no

 

On search # 1 the product name 'nike air' contains the search term 'nike', so for my purposes that is a match. 

 

The problem is that on search #2 even though the search term and product name are exact, it's still saying that it isn't a match. I thought maybe case was an issue, so i accounted for that (the product name is always uppercase so I made the search term uppercase).  I also thought maybe leading trailing spaces were an issue, so I accounted for that (with trim).  But it's still not working.

 

I'm guessing there's a flaw in my code?

 

IF [TradeName] != NULL()
AND [searchterm] != ''
AND CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'Yes'
ELSEIF [TradeName] != NULL()
AND [searchterm] != ''
AND NOT CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'No'
ELSE Null()
ENDIF

ACE Emeritus
ACE Emeritus

HI @rhyatt,

I'm just curious, in the example given, what if you were to do UPPERCASE on both the lookup term and the product term?

Alteryx Partner

I tried to re-create the issue but it works fine... try it ( I have add @JohnJPS suggestion but it did work before).

 

Are you sure that the strings you are comparing do not have any other special character other than space (tab?)?

 

when you check the data, try to tick the highlighted symbol to see if you have any "weird" chars in your data

Capture.JPG

 

 

 

 

Asteroid

Just tried that, unfortunately the same result.  It still doesn't want to work with that space in there.

Asteroid

Hmmm I don't have that symbol as an option in my results?

 

alteryxresults.png

Alteryx Partner

double click on one field (string) it will appear hopefully :)

Highlighted
Asteroid

Ok thanks, got the tool...and I see what you're talking about.

 

So for this example,it says the terms don't match.

the search term shows as: lithium carbonate

and the trade name shows a dot character in-between the words: lithium *carbonate (it actually shows a dot, but i just used an asterisk for my visual example).

 

So you've helped me identify the issue! Now I'm just not sure what to do to fix it...??

 

EDIT**

 

I think I found the solution.  I used the Data Cleansing tool, and under the section of REMOVE UNWANTED CHARACTERS I checked the box for 'tabs, linebreaks, and duplicate whitespace'.  So far it looks like it's working....

Alteryx Partner

Sorry for not coming back to you, I have missed your answer.

 

Glad to hear you solved it.

Labels