Choose Language Hide Translation Bar
Highlighted
miguello
Level V

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
Highlighted
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 || "\!" , \!"" || :b || "\!", "

 

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
Highlighted
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 || "\!" , \!"" || :b || "\!", "

 

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

Highlighted
miguello
Level V

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.
Article Labels

    There are no labels assigned to this post.