- Have you ever seen “~=” or “!=” used in Spotfire expressions and not know what this syntax means?
Since I am in Houston this week working in the Ruths.ai office helping to update training, my blog post is going to be a bit short. I thought I would focus on custom expression syntax the Excel turned Spotfire expert might not be familiar with.
- “~=” means contains
- “!=” mean not equal to
The “~=” is especially helpful when you are trying to pull a few words or specific characters out of a long string of text. In the screen shot below I am looking at Lending Club data on loans, and one of the columns available is called emp_title that represents the borrower’s job title. In the screen shot below, I used an IF statement to identify borrowers working in banking. Even though their title may be long and wordy, I can test against just the word “bank”.
If([emp_title]~=”bank”,”FINANCE”,”OTHER”)
The syntax “!=” is the same as <>. However, I wanted to point it out because I’ve seen it used in blog posts, and it helpful for users to understand.
In the same data set, there is a column called loan_status that has 9 different statuses. I have used the IF statement shown below to consolidate those statuses into only two statuses using “!=”.
If([loan_status]!=”Fully Paid”,”Outstanding”,”Fully Paid”)
In the coming weeks, I’ll be putting up posts about using relations and column matches, and then a series on interpreting the results of Spotfire’s predictive models. If you have any questions, feel free to reach out to me at julie@bigmountainanalytics.com. Have a good week!
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!
What will be syntax for not like?
apposite of contains = not contains
Hi Amol,
The contains function (~=) is designed to be in an if or case statement. So, to get the alternative case (does not contain), just use the negative argument in the if statement.
In Julie’s example, the “OTHER” label will denote anything that does not contain the word “bank”.
Here is a great resource for Spotfire text functions:
https://docs.tibco.com/pub/spotfire/6.5.0/doc/html/ncfe/ncfe_text_functions.htm
Thanks,
Jason