Assign values containing in the list

[#1]

Hi,

this problem really puzzles me.

I want to assign values from table 1 (dt_group) to table 2 (dt_subset). 

So iterate through each row of Table 2 and then compare the rows of the table 2 Product column with each row of table 1 ProductGroups and then assign the Limits values to the _LIMITS column in table 1 if matching sting is found. How would I script this?

table 1:

table 2:

I already tried and have problem with comparing strings from both tables...

Names Default To Here( 1 );
dt_subset = Current Data Table();
col_names = dt_subset << get column names( string );
dt_group = Data Table( "dt_groups" );
aa_limits = Associative Array( dt_group:Tests << get values, dt_group:Limits << get values );

For( i = 1, i <= N Items( aa_limits ), i++, 
	
	If( Contains( aa_limits, col_names[i] ),
		curname = Column( dt_subset, col_names[i] ) << get name;
						
						
		new_col = dt_subset << New Column( (curname || "_LIMITS"),
			Numeric,
			Continuous,
			Format( "Percent", 12, 2 )
							
		);
		dt_subset << Move Selected Columns( new_col, After( Column( dt_subset, curname ) ) );
				
	)
);

Thanks,

J

[#2]

Here is how I would do what you have specified

Names Default To Here( 1 );
dt_subset = Current Data Table();
col_names = dt_subset << get column names( string );
dt_group = Data Table( "dt_groups" );
//aa_limits = Associative Array( dt_group:Tests << get values, dt_group:Limits << get values );

For( i = 1, i <= N Rows( dt_group ), i++,
	found = Contains( col_names, dt_group:Tests[i] );
	If( found  > 0, 
		//curname = Column( dt_subset, col_names[i] ) << get name;
		Eval(
			Eval Expr(
				new_col = dt_subset << New Column( col_names[found] || "_LIMITS",
					Numeric,
					Continuous,
					Format( "Percent", 12, 2 ),
					set each value( Expr( dt_group:limits[i] ) )
							
				)
			)
		);
		dt_subset << Move Selected Columns( new_col, After( Column( dt_subset, col_names[found] ) ) );
				
	)
);

Make sure your names in the dt_group:tests column match the column names.  You may have some extra blank spaces in column names compared to the Tests values.

[#3]

Thanks, Jim! Not exactly what I am looking for.

An example:

compare Product Column row 1 of table 2 with all the strings in the first row of ProductGroups and then assign the corresponding limit value

[#4 · Accepted solution]

Here is a completely different approach.  It uses the Join capabilities in JMP rather than attempting to do everything with loops and comparisons.  I believe it is going what you want, but please verify the results.

Names Default To Here( 1 );
dt_subset = Data Table( "subset table" );
col_names = dt_subset << get column names(  string,continuous );
dt_group = Data Table( "dt_groups" );

// Expand dt_group:ProductGroups into separate columns
dt_group << text to columns( delimiter( "," ), columns( :ProductGroups ) );

// Stack the resulting columns
dt_group_col_names = dt_group << get column names( string );
// Remove the first 3 columns from the list
Remove From( dt_group_col_names, 1, 3 );
// Stack data table
// → Data Table( "Stacked dt_groups" )
dt_stack_group = dt_group << Stack(
	columns( dt_group_col_names ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Product" ),
	"Non-stacked columns"n( Keep( :Tests, :Limits ) ),
	Output Table( "Stacked dt_groups" )
);
// Get rid of the Label column
dt_stack_group << delete columns( :Label );

// Stack the subset table
// Add a RowNum column to the table to preserve the order
dt_subset << New Column( "RowNum", set each value( Row() ) );

dt_stack_subset = dt_subset << Stack(
	columns(
		col_names
	),
	Source Label Column( "Tests" ),
	Stacked Data Column( "Data" ),
	Name("Non-Stacked columns")(Keep({Product, RowNum})),
	Output Table( "Stack of subset table" )
);

// Join the 2 stacked tables
dt_joined = dt_stack_subset << Join(
	With( dt_stack_group ),
	Merge Same Name Columns,
	Match Flag( 0 ),
	By Matching Columns( :Product = :Product, :Tests = :Tests ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Joined stack data" )
);

// Split the table back to original form
dt_final = dt_joined << Split(
	Split By( :Tests ),
	Split( :Data, :Limits ),
	Group( :RowNum ),
	Output Table( "Final" ),
	Sort by Column Property
);

// Cleanup and rearrange the Final data table
dt_final << delete columns( :RowNum );
// Rename the columns
For( i = 2, i <= N Cols( dt_final ), i++,
	theName = Column( dt_final, i ) << get name;
	If(
		Starts With( theName, "Data" ), Column( dt_final, i ) << set name( Substr( theName, 6 ) ),
		Starts With( theName, "Limits" ),
			Column( dt_final, i ) << set name( Substr( theName, 8 ) || "_LIMITS" )
	);
);
// Reorder the columns
For( i = Length( col_names ), i >= 1, i--,
	If( Try( Column( dt_final, col_names[i] || "_LIMITS" ) << get name, "" ) != "",
		theName = ":\!"" || col_names[i] || "_LIMITS" || "\!"n";
		Eval( Parse( "dt_final << Move Selected Columns( " || theName || ",to first);" ) );
		//dt_final << Move Selected Columns( :"Test5^0.8_LIMITS"n,to last)
	);
	theName = ":\!"" || col_names[i] || "\!"n";
	Eval( Parse( "dt_final << Move Selected Columns( " || theName || ",to first);" ) );
);
dt_final << move selected columns( :Product, to first );

close( dt_stack_group, nosave);
close( dt_stack_subset, nosave);
close( dt_joined, nosave);

[#5 · Accepted solution]

Here is my attempt. It uses the provided tables so it doesn't create any new columns and just temporarily sets formulas to calculate the values (most likely can be simplified quite a lot if subset table doesn't have pre-created columns)

Names Default To Here(1);
dt_subset = Open("$DOWNLOADS/subset table.jmp");
dt_group = Open("$DOWNLOADS/dt_groups.jmp");

aa_tests = Associative Array();

For Each Row(dt_group,
	limitname = :Tests || "_LIMITS";
	aa_tests[limitname] = Associative Array();
	aa_tests[limitname]["Groups"] = Words(:ProductGroups, " ,");
	aa_tests[limitname]["Limit"] = :Limits
);


colnames = dt_subset << Get Column Names("Continuous", "String");
For Each({{testname, specs}}, aa_tests,
	If(!Contains(colnames, testname),
		Write("\!NNo Match: " || testname);
		Continue();
	);
	Column(dt_subset, testname) << Formula(
		If(Contains(specs["Groups"], :Product),
			specs["Limit"];
		,
			.
		);
	);
	dt_subset << run formulas;
	Column(dt_subset, testname) << Delete Formula;
);

[#6]

Thanks Jim and Jarmo!

Here's another method I used to figure it out.

Names Default To Here( 1 );
dt_subset = Data Table( "subset table.jmp" );
dt_group = Data Table( "dt_groups.jmp" );
list1 = dt_group:Limits << get values;
list2 = dt_group:ProductGroups << get values;
list3 = dt_group:Tests << get values;

col_names = dt_subset << get column names( string );

aa_limits = Associative Array( dt_group:Tests << get values, dt_group:Limits << get values );


For Each( {val1, idx1}, list2, 
					
	For Each Row(
		dt_subset, 
									
		For Each( {val3, idx2}, list3, 

			If( Contains( val1, dt_subset:Product ), 
								
				As Column( dt_subset, list3[idx1] || "_LIMITS" ) = list1[idx1]
			
			)	
								
						
		)
	);
	
);