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

[#1]

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, 

[#2]

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.

[#3]

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, 

[#4]

You could use virtual joins via the column properties.

[#5]

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.

[#6]

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, 

[#7 · Accepted solution]

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;
);

[#8 · Accepted solution]

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...)

View more...

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

1. Start with purely JMP provided scripts
2. Improve them slightly (for example utilize variables instead of "hard-coded" table names)
3. 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)
4. Finally end up with something like my solution which is still partially utilizing JMP provided JSL (opening tables, updating table)

[#9]

Thank you, Jim and Jarmo,

I learn a lot from the discussion community. Thank you for your help, I tried to find topics that related to the thing that I want to do and I got the answers from this community. The scripts that JIM and you give to me are work. However, my real data is more complicated than the examples. Such as, if I use the same dictionary with multiple columns in the dataset, such as I attached the new dataset1 and dictionary1. Can you correct the script for this, if 1 column is shared with other columns in the same pattern? Such as Dictionary A and Columns A01, A08, A9,...

I attached the new dataset here. 

Best, 

[#10]

For this case, just add a mapping to get the right subset, e.g. in Jarmos Code:

colMapping = Associative array({{"SEX","SEX"},{"NAME","NAME"},{"A09:05","A"},{"A01:01","A"},{"DPA05","DP"},{"DPB04","DP"}});
colIndex= colMapping[col];
dt_dict_subset = dt_dict << Subset(Rows(dt_dict << get rows where(:Column == colIndex)), Selected Columns(0), Invisible);

Alternative idea:   There is a functionality in Recode which gets quite close to what you want: Apply Mapping from Table

At the moment it doesn't have a setting to specify the right subset. So, if you apply such mappings regularly  - and other users do as well - you might want to post a wish in the Wish List to enhance the function by such a setting ...