cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
VCO
VCO
Level III

How to find a specific text (word) within a column character - nominal data type ?

Hi,

 

I'd like to get some help on finding a specific string (actually 2 strings, say "JMP1" and "JMP2") that may be within a column with text, as we move down that column, and then have a formula column next to it to label "JMP1" or "JMP2", if it finds it, or label say "NA" otherwise. 

 

A couple of notes:

1- "JMP1" or "JMP2" can be in any position within the text in Column 1

2- "JMP1" and "JMP2" are not contained in the same text in Column 1

 

So in my mind it will be something like this for the Formula Column (Column 2 - the label column):

 

IF (Column 1 contains "JMP1", then Column 2 = "JMP1", else (IF Column 1 contains "JMP2", then Column 2 = "JMP2", else Column 2 = "NA"));

 

I looked up to the Match function, but I got a bit confused with the multiple arguments and expressions. Maybe I'm not on the right track either, hence why I need advice

 

It will be great if someone could share their thoughts (preferably through the Formulas area, but will be open to suggestions through JSL).

 

Thanks,

Vasco

1 ACCEPTED SOLUTION

Accepted Solutions
KarenC
Super User (Alumni)

Re: How to find a specific text (word) within a column character - nominal data type ?

If( Contains( :name, "JA" ),
	"JA",
	If( Contains( :name, "MA" ),
		"MA",
		"NA"
	)
)

I think you are there.  JMP has a character function called contains.  You can use that as illustrated above for the big class sample data file. See the help for character functions at character function help 

View solution in original post

5 REPLIES 5
KarenC
Super User (Alumni)

Re: How to find a specific text (word) within a column character - nominal data type ?

If( Contains( :name, "JA" ),
	"JA",
	If( Contains( :name, "MA" ),
		"MA",
		"NA"
	)
)

I think you are there.  JMP has a character function called contains.  You can use that as illustrated above for the big class sample data file. See the help for character functions at character function help 

VCO
VCO
Level III

Re: How to find a specific text (word) within a column character - nominal data type ?

Hi @KarenC ,

 

Thanks, it worked !  Simple and straightforward  :) 

 

I actually looked at the Contains function in the Character functions' help, but was getting confused with the description as it says that it will retrieve a "numeric position". Your solution was pretty much what I mentioned on my post, just had to converted into an actual JMP formula using the contains function.

 

Cheers,

Vasco

KarenC
Super User (Alumni)

Re: How to find a specific text (word) within a column character - nominal data type ?

Ah, I see the confusion. So since we used the full string that you were looking for in the contains function we got either a "1" as the string started in position 1 or a 0 since the string didn't start in any position. This worked in the If statement. If we had used Contains(:column, "P") it would have returned "3" for any row with a JMPx value, since P is in the third position. I didn't think (or really know) about that nuance as I typically use the contain function as you were trying to do...is something there or not. The Regex solution is more sophisticated and is a good solution for something that is going to be used repeatability with changing data.
vince_faller
Super User (Alumni)

Re: How to find a specific text (word) within a column character - nominal data type ?

Regex is really powerful and used in a lot of languages. Though it can admittedly be quite hard to understand.  

 

Names default to here(1);
dt = new table("Example", 
	New Column("JMPs", character, <<Set Values(repeat({"JMP1", "JMP2", "JMP14", "Something Else"}, 5)));
);

dt << new Column("Simple Regex with Or", 
	character, 
	formula(
		// 14 needs to be first because JMP1 would also match
		r = regex(:JMPs, "JMP14|JMP1|JMP2");
		if(!ismissing(r), 
			r
		, // else
			"NA"
		)
	)
);

// a more compliated example
// if you know something about the form you can do some neat stuff

dt << new Column("regex with pattern", 
	character, 
	formula(
		// this looks for "JMP" than any number of digits so you don't have to make a billion if statements
		r = regex(:JMPs, "JMP\d+");
		if(!ismissing(r), 
			r
		, // else
			"NA"
		)
	)
);
Vince Faller - Predictum
VCO
VCO
Level III

Re: How to find a specific text (word) within a column character - nominal data type ?

Hi @vince_faller ,

 

I must admit that your proposal is a bit too much advanced for me at this point.Thanks for your input., though  :) 

 

I really liked Karen's simple proposal for my problem.

 

Regards,

Vasco