nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to link the column code from one table to another table dataset

doraemengs

Hi everyone, I have 2 data tables. My dataset table "Dataset.jmp" had the column data as a code "ENICITY" and "ETHCAT" that I need to link with dataset "Dictionary.jmp" that can change the code into labels (in the Labels column of Dictionary file). My actual dataset had 50 categories, so I cannot use the function "Value Labels" of all categories. Any idea for linking the row of one table to the column of another table with the exact value? the column that I want is the result column on Dataset.jmp file

I attached the files here.

Thank you so much!

Best,

Dictionary.jmp

Dataset.jmp

Accepted answers

txnelson

Here is a good start on what you want. You should be able to expand on this to meet and discrepancies in what your final requirements are.

If you haven't taken the time to read the Scripting Guide available under JMP Help, I strongly suggest that you do that. Also, make sure your study the example below, so you understand the logic and the specifics of the functions and statements.

names default to here(1);

// Open Data Table: Dataset.jmp
// → Data Table( "Dataset.jmp" )
dt = Open( "$Downloads/Dataset.jmp" );


// Open Data Table: Dictionary.jmp
// → Data Table( "Dictionary.jmp" )
dtDict = Open( "$Downloads/Dictionary.jmp" );


/*// Subset data table
// → Data Table( "Subset of Dictionary" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 1, 10 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );


// Subset data table
// → Data Table( "Subset of Dictionary 2" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 11, 14 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );*/

// Change column modeling type: Values
Data Table( "Dictionary" ):Values << Set Modeling Type( "Continuous" );

// Create a new dictionary data table for each level of the Column column
dtDictList = dtDict << Subset(
	By( :Column ),
	All rows,
	Selected columns( 0 ),
	Selected rows( 0 )
);

/*// Change column info: Values
Data Table( "Dictionary.jmp" ):Values << Data Type( Numeric ) <<
Set Field Width( 12 );*/

// Clear row selection
//Data Table( "Dataset.jmp" ) << Clear Select;
dt << Clear Column Selection();

// Change column modeling type: ETHNICITY
Data Table( "Dataset.jmp" ):ETHNICITY << Set Modeling Type( "Continuous" );

// Change column modeling type: ETHCAT
Data Table( "Dataset.jmp" ):ETHCAT << Set Modeling Type( "Continuous" );


/*// Change column modeling type: Values
Data Table( "Subset of Dictionary 2" ):Values << Set Modeling Type( "Continuous" );


// Change column modeling type: Values
Data Table( "Dictionary.jmp" ):Values << Set Modeling Type( "Continuous" );*/

For Each( {thedt, index}, dtDictList, 
	// Get the dataset column to join with
	joinColumn = Word( 3, Char( thedt ), "=\!"" );
	// Join data tables
	// → Data Table( "untitled 1925" )
	newDT = dt << Join(
		With( thedt ),
		Merge Same Name Columns,
		Match Flag( 0 ),
		By Matching Columns( Column( joinColumn ) = :Values ),
		Drop multiples( 0, 0 ),
		Include Nonmatches( 1, 0 ),
		Preserve main table order( 1 ),
		Output Table( "Join of Dataset with Column=ETHCAT" )
	);
	NewDt:Labels << set name( joinColumn || " Labels" );
	NewDt:Values << set name( joinColumn || " Values" );
	If( index > 1,
		Close( dt, nosave )
	);
	dt = newDT;
);

jthi

I would definitely start with the solution Jim provided as it is utilizing more of JMP provided JSL, but this is something I would write after a lot of experience with JSL and JMP (part of my job is automating JMP with scripting and to add extra features to JMP with JSL...)

Names Default To Here(1);

dt_data = Open("$DOWNLOADS/Dataset.jmp");
dt_dict = Open("$DOWNLOADS/Dictionary.jmp");
Column(dt_dict, "Values") << Set Data Type("Numeric");

/*
// Not really sure if this "fix" should be done or not
missing_rows = Loc(dt_dict[0, "Values"], .);
If(N Rows(missing_rows) > 0,
	dt_dict[missing_rows, "Values"] = 0;
);
*/

Summarize(dt_dict, cols = By(:Column));

For Each({col}, cols,
	dt_dict_subset = dt_dict << Subset(Rows(dt_dict << get rows where(:Column == col)), Selected Columns(0), Invisible);
	Column(dt_dict_subset, "Labels") << Set Name("Result " || col);
	dt_data << Update(
		With(dt_dict_subset),
		Match Columns(Eval(col) = :Values),
		Add Columns from Update Table(Eval("Result " || col))
	);
	Close(dt_dict_subset, no save);
);

Write();

Names Default To Here(1); dt_data = Open("$DOWNLOADS/Dataset.jmp"); dt_dict = Open("$DOWNLOADS/Dictionary.jmp"); Column(dt_dict, "Values") << Set Data Type("Numeric"); /* // Not really sure if this "fix" should be done or not missing_rows = Loc(dt_dict[0, "Values"], .); If(N Rows(missing_rows) > 0, dt_dict[missing_rows, "Values"] = 0; ); */ Summarize(dt_dict, cols = By(:Column)); For Each({col}, cols, dt_dict_subset = dt_dict << Subset(Rows(dt_dict << get rows where(:Column == col)), Selected Columns(0), Invisible); Column(dt_dict_subset, "Labels") << Set Name("Result " || col); dt_data << Update( With(dt_dict_subset), Match Columns(Eval(col) = :Values), Add Columns from Update Table(Eval("Result " || col)) ); Close(dt_dict_subset, no save); ); Write();

First, you should definitely fully understand the option Jim provided. Then you can maybe start thinking about utilizing something like this (if you have okeish understanding programming) as this is basically skipping most of the code JMP can provide you with (most of Open() and << Update are JMP provided).

Do not directly skip to my solution before going through earlier steps if you wish to learn JSL and how to automate JMP

Start with purely JMP provided scripts
Improve them slightly (for example utilize variables instead of "hard-coded" table names)
Start adding some "programming" to the mix". This is where I would consider Jim's solution belongs to (for example loop which JMP most likely won't be able to provide you with and utilizing Word() function)
Finally end up with something like my solution which is still partially utilizing JMP provided JSL (opening tables, updating table)

hogi

As another alternative - without any subsetting/For Each:

names default to here(1);
dict = Open( "$DOWNLOADS/Dictionary1.jmp" );
dt = Open( "$DOWNLOADS/Dataset1.jmp" );

// stack the data for easy application of the dictionary
dt2=dt << Stack(
	columns(
		:ETHNICITY, :ETHCAT, :SEX, :NAME, :"A09:05"n, :"A01:01"n, :DPA05, :DPB04
	),
	Source Label Column( "col" ),
	Stacked Data Column( "values" )
);

// add a column with the corrected column-identifiers
dt 2 << New Column( "column",
	Character,
	Formula(
		Match( :col,
			"A01:01", "A",
			"A09:05", "A",
			"DPA05", "DP",
			"DPB04", "DP",
			:col
		)
	)
);
<BR />// add the labels from the disctionary
dt2 <<
Update(
	With( dict ),
	Match Columns( :column = :Column, :values = :Values ),
	Add Columns from Update Table( :Labels )
);<BR />
// convert back to a wide table
dt2 <<
Split(
	Split By( :col ),
	Split( :Labels ),
	Group( :ID ),
	Remaining Columns( Drop All )
);

All comments

txnelson

Look into

Tables=>Join

Subset data table Dictionary by the Column column into a table for ETHCAT and one for ETHNICITY. Change the Value column into a numeric column. For the ETHNICITY data table, the Null or Missing needs to be expanded into 2 rows with Value having the value of . and one having the value of 0

Join each of the subsetted tables to the data table called dataset, joining ETHNICITY with Value for the one table, and ETHCAT with Value for the second table.

doraemengs

Hi Jim, Thank you for your idea. However, I have more than 50 columns and 1000 rows, do you have any idea about a larger dataset?

Best,

hogi

You could use virtual joins via the column properties.

txnelson

Why would the number of rows and columns create an issue. If the number of different lookups (ETHCAT, ETHNICITY etc.) are more, the subsetting and merging would just have to be placed into a loop. However, the start is to do it interactively for 1 or 2 groups, and then to take the JSL that JMP produces to do those operations, and place them into a script. Then the looping of the steps can be added to the script.

doraemengs

Hi Jim, Thank you so much. Your suggestions work well. However, if I have a dictionary of 150 rows with different categories (various from 5 to 30 groups, such as ETHCAT, ETHNICITY, etc.), and a column on the dataset table that needs to join with 300 columns. To be honest, It is hard to do that manually and I have multiple tables that join together. Can you provide the script on 1. How to categorize the same thing in a dictionary file, and how to join all of the dataset columns with a dictionary only 1 file. This is the script that I try to do. However, I cannot modify the scripts.

// Open Data Table: Dataset.jmp
// → Data Table( "Dataset.jmp" )
Open( "$DOWNLOADS/Dataset.jmp" );


// Open Data Table: Dictionary.jmp
// → Data Table( "Dictionary.jmp" )
Open( "$DOWNLOADS/Dictionary.jmp" );


// Subset data table
// → Data Table( "Subset of Dictionary" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 1, 10 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );


// Subset data table
// → Data Table( "Subset of Dictionary 2" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 11, 14 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );


// Change column info: Values
Data Table( "Subset of Dictionary 2" ):Values << Data Type( Numeric ) <<
Set Field Width( 12 );


// Change column info: Values
Data Table( "Dictionary.jmp" ):Values << Data Type( Numeric ) <<
Set Field Width( 12 );


// Clear row selection
Data Table( "Dataset.jmp" ) << Clear Select;


// Change column modeling type: ETHNICITY
Data Table( "Dataset.jmp" ):ETHNICITY << Set Modeling Type( "Continuous" );


// Change column modeling type: ETHCAT
Data Table( "Dataset.jmp" ):ETHCAT << Set Modeling Type( "Continuous" );


// Change column modeling type: Values
Data Table( "Subset of Dictionary 2" ):Values << Set Modeling Type( "Continuous" );


// Change column modeling type: Values
Data Table( "Dictionary.jmp" ):Values << Set Modeling Type( "Continuous" );


// Join data tables
// → Data Table( "untitled 1925" )
Data Table( "Dataset.jmp" ) << Join(
	With( Data Table( "Subset of Dictionary 2" ) ),
	By Matching Columns( :ETHCAT = :Values ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 )
);

Thank you so much,

Best,

txnelson

Here is a good start on what you want. You should be able to expand on this to meet and discrepancies in what your final requirements are.

names default to here(1);

// Open Data Table: Dataset.jmp
// → Data Table( "Dataset.jmp" )
dt = Open( "$Downloads/Dataset.jmp" );


// Open Data Table: Dictionary.jmp
// → Data Table( "Dictionary.jmp" )
dtDict = Open( "$Downloads/Dictionary.jmp" );


/*// Subset data table
// → Data Table( "Subset of Dictionary" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 1, 10 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );


// Subset data table
// → Data Table( "Subset of Dictionary 2" )
Data Table( "Dictionary.jmp" ) << Clear Select << Select Rows( Index( 11, 14 ) ) <<
Subset( Selected Rows( 1 ), Selected columns only( 0 ) );*/

// Change column modeling type: Values
Data Table( "Dictionary" ):Values << Set Modeling Type( "Continuous" );

// Create a new dictionary data table for each level of the Column column
dtDictList = dtDict << Subset(
	By( :Column ),
	All rows,
	Selected columns( 0 ),
	Selected rows( 0 )
);

/*// Change column info: Values
Data Table( "Dictionary.jmp" ):Values << Data Type( Numeric ) <<
Set Field Width( 12 );*/

// Clear row selection
//Data Table( "Dataset.jmp" ) << Clear Select;
dt << Clear Column Selection();

// Change column modeling type: ETHNICITY
Data Table( "Dataset.jmp" ):ETHNICITY << Set Modeling Type( "Continuous" );

// Change column modeling type: ETHCAT
Data Table( "Dataset.jmp" ):ETHCAT << Set Modeling Type( "Continuous" );


/*// Change column modeling type: Values
Data Table( "Subset of Dictionary 2" ):Values << Set Modeling Type( "Continuous" );


// Change column modeling type: Values
Data Table( "Dictionary.jmp" ):Values << Set Modeling Type( "Continuous" );*/

For Each( {thedt, index}, dtDictList, 
	// Get the dataset column to join with
	joinColumn = Word( 3, Char( thedt ), "=\!"" );
	// Join data tables
	// → Data Table( "untitled 1925" )
	newDT = dt << Join(
		With( thedt ),
		Merge Same Name Columns,
		Match Flag( 0 ),
		By Matching Columns( Column( joinColumn ) = :Values ),
		Drop multiples( 0, 0 ),
		Include Nonmatches( 1, 0 ),
		Preserve main table order( 1 ),
		Output Table( "Join of Dataset with Column=ETHCAT" )
	);
	NewDt:Labels << set name( joinColumn || " Labels" );
	NewDt:Values << set name( joinColumn || " Values" );
	If( index > 1,
		Close( dt, nosave )
	);
	dt = newDT;
);

jthi

Names Default To Here(1);

dt_data = Open("$DOWNLOADS/Dataset.jmp");
dt_dict = Open("$DOWNLOADS/Dictionary.jmp");
Column(dt_dict, "Values") << Set Data Type("Numeric");

/*
// Not really sure if this "fix" should be done or not
missing_rows = Loc(dt_dict[0, "Values"], .);
If(N Rows(missing_rows) > 0,
	dt_dict[missing_rows, "Values"] = 0;
);
*/

Summarize(dt_dict, cols = By(:Column));

For Each({col}, cols,
	dt_dict_subset = dt_dict << Subset(Rows(dt_dict << get rows where(:Column == col)), Selected Columns(0), Invisible);
	Column(dt_dict_subset, "Labels") << Set Name("Result " || col);
	dt_data << Update(
		With(dt_dict_subset),
		Match Columns(Eval(col) = :Values),
		Add Columns from Update Table(Eval("Result " || col))
	);
	Close(dt_dict_subset, no save);
);

Write();

Do not directly skip to my solution before going through earlier steps if you wish to learn JSL and how to automate JMP

Start with purely JMP provided scripts
Improve them slightly (for example utilize variables instead of "hard-coded" table names)
Start adding some "programming" to the mix". This is where I would consider Jim's solution belongs to (for example loop which JMP most likely won't be able to provide you with and utilizing Word() function)
Finally end up with something like my solution which is still partially utilizing JMP provided JSL (opening tables, updating table)

All comments

Quick Links