Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need help with CONTAINS function

rhyatt
8 - 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

7 REPLIES 7
JohnJPS
15 - Aurora

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?

mborriero
11 - Bolide

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

 

 

 

 

rhyatt
8 - Asteroid

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

rhyatt
8 - Asteroid

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

 

alteryxresults.png

mborriero
11 - Bolide

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

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

mborriero
11 - Bolide

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

 

Glad to hear you solved it.

Labels