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
miguello
Level VI

Easiest way to make two columns into a match formula?

All, 

 

Simple task, but on a great scale.

I have a table with two columns that have two different naming conventions for the same thing. Few hundreds of rows. Let's say something like that:

NATO reporting name Common name

FaceplateMikoyan-Gurevich Ye-2A
**bleep**otMikoyan-Gurevich MiG-15
FangLavochkin La-11
FantailLavochkin La-15
FantanNanchang Q-5/A-5
FargoMikoyan-Gurevich MiG-9
FarmerShenyang J-6 and Mikoyan-Gurevich MiG-19 [1]
FeatherYakovlev Yak-15/Yak-17
FencerSukhoi Su-24
FiddlerTupolev Tu-28/Tu-128
FinLavochkin La-7
FinbackShenyang J-8
FirebarYakovlev Yak-28P
FirebirdChengdu J-10
Fire FangChengdu J-20
FirkinSukhoi Su-47
FishbedMikoyan-Gurevich MiG-21
FishcanChengdu J-7[2]
FishpotSukhoi Su-9 and Su-11

 

and so on. Now I need an easy way of creating a Match formula out of these columns. Any suggestion rather than parsing the table externally and adding pieces of formula around it?

The reason I need a formula, and not a Virtual Join or something is that I have a column that have mixed names, some are Nato reporting name some are common names. Formula would work on Nato names, changing it to Common Names, leaving Common Names untouched.

 

Also, any other way of reducing two naming conventions in one column to one if I have a table of matched names from both conventions?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Easiest way to make two columns into a match formula?

It's pretty easy to manipulate the columns of the table into the bones of the Match() codes that you need by concatenating the two columns along with some double quotes (") and commas (,) with a formula like this:

 

"\!"" || :a || "\!" , \!"" || :p || "\!", "

 

JMPScreenSnapz018.png

Then, just highlight the new column with the concatenated values in the data table and choose Edit->Copy to get those values onto the clipboard.

 

Now, you can open your new Formula Editor where you want the Match(), add the Match function, set the match column appropriately and then double click on the Match function to get the JSL editor for it. Paste over the two Empty() functions with your copied values. Be sure to remove the trailing comma (,).

 

ScreenFlow.gif

-Jeff

View solution in original post

2 REPLIES 2
Jeff_Perkinson
Community Manager Community Manager

Re: Easiest way to make two columns into a match formula?

It's pretty easy to manipulate the columns of the table into the bones of the Match() codes that you need by concatenating the two columns along with some double quotes (") and commas (,) with a formula like this:

 

"\!"" || :a || "\!" , \!"" || :p || "\!", "

 

JMPScreenSnapz018.png

Then, just highlight the new column with the concatenated values in the data table and choose Edit->Copy to get those values onto the clipboard.

 

Now, you can open your new Formula Editor where you want the Match(), add the Match function, set the match column appropriately and then double click on the Match function to get the JSL editor for it. Paste over the two Empty() functions with your copied values. Be sure to remove the trailing comma (,).

 

ScreenFlow.gif

-Jeff
miguello
Level VI

Re: Easiest way to make two columns into a match formula?

Awesome, thanks! Exactly what I had in mind, for some reason got stuck on constructing formula code by external means when I could easily do that in the table itself.