Subscribe Bookmark RSS Feed

Assign unique ID for each unique text

qais_hatim_fda_

Community Trekker

Joined:

Sep 22, 2015

Hi all,

 

I would like to get your help in the following case. I have a data that dealing with drugs and their specific problems within different firms. The two columns (i.e. drug name and problem specifications) are text data. I would like to create a third column (continous one) that add unique ID to each drug name when it appears. For instance, Piperacillin Sodium and Tazobactam Sodium is drug name. When ever this name appear assign ID=1. If the drug name changed to Zoledronic Acid, for instance, ID=2 and so on so forth. Could you please help me in building formula in JMP to address this case? Best, Qais

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is a script that runs against a sample data table that creates an ID column with a formula as you want

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\cars.jmp" );
dt << New Column( "ID",
	formula(
		If( Row() == 1,
			Summarize( drugs = by( :Make ) )
		);
		Contains( drugs, :Make );
	)
);
Jim
M_Anderson

Staff

Joined:

Nov 21, 2014

Solution
There is an add-in in the Add-in Depot that basically does this. It's called "compress to labeled code." You want the revised version. Run the add-in on the column with the text. This will create a new column called "<column name> coded". Go into the column properties and turn off the value labels and click apply. You should be left with what you are after.

Best,

M
5 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is a script that runs against a sample data table that creates an ID column with a formula as you want

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\cars.jmp" );
dt << New Column( "ID",
	formula(
		If( Row() == 1,
			Summarize( drugs = by( :Make ) )
		);
		Contains( drugs, :Make );
	)
);
Jim
qais_hatim_fda_

Community Trekker

Joined:

Sep 22, 2015

Thank you so much Sir for your amazing professional help. Problem solved. Any recommendation in improving my skills in writing such advance JSL formula?

Best,
Qais
txnelson

Super User

Joined:

Jun 22, 2012

My suggestion is to read the documentation provided by JMP.  Of the several books downloaded when you install JMP:

     Help==>Books

I suggest that you read,

     Using JMP

     Basic Analysis

     Essential Graphing

     Scripting Guide

 

This is a lot of reading, however, JMP is a powerful product with a lot of capabilities

     

Jim
M_Anderson

Staff

Joined:

Nov 21, 2014

Solution
There is an add-in in the Add-in Depot that basically does this. It's called "compress to labeled code." You want the revised version. Run the add-in on the column with the text. This will create a new column called "<column name> coded". Go into the column properties and turn off the value labels and click apply. You should be left with what you are after.

Best,

M
qais_hatim_fda_

Community Trekker

Joined:

Sep 22, 2015

Thank you so much. Well done