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 · Accepted solution]

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

// add the labels from the disctionary
dt2 <<
Update(
	With( dict ),
	Match Columns( :column = :Column, :values = :Values ),
	Add Columns from Update Table( :Labels )
);

// convert back to a wide table
dt2 <<
Split(
	Split By( :col ),
	Split( :Labels ),
	Group( :ID ),
	Remaining Columns( Drop All )
);

[#3]

Thank you Hogi, However, can you provide the script that does not need to retype all of the columns that we have (more than 50 columns)? 

Thank you !

[#4]

With match fyou just have to list columns where there is a change, the remaining columns stay 'col' - last entry.

If you don't want to srt up a mapping, just use matching names for the columns and the dictionary.

Alternatively, use a consistent mapping (like: first character of the column name. Or first 2 characters) and a corresponding equation.