Can matching data be obtained directly without auxiliary columns?

lwx228

For example, use the "age" column and "sex" column combinations of "Big class.jMP" to generate new column names on another table.
How to match the data of another table directly through "age" and "sex" column without adding auxiliary column in the big table?

 

Thanks!

txnelson

Try this

names default to here(1);

// Create the 2 Example data tables
dtBig = open("$SAMPLE_DATA/big class.jmp");
dtSmall = New Table( "Data agesex",
	Add Rows( 12 ),
	New Column( "agesex",
		Character,
		"Nominal",
		Set Values(
			{"12F", "12M", "13F", "13M", "14F", "14M", "15F", "15M", "16F", "16M",
			"17F", "17M"}
		)
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 0 ),
		Set Values( [5, 3, 3, 4, 5, 7, 2, 5, 2, 1, 1, 2] )
	)
);

// Create the new column
dtBig << New Column( "N Rows" );

// Move the data as required
For( i = 1, i <= N Rows( dtSmall ), i++,
	foundRows = dtBig << get rows where(
		Num( Substr( dtSmall:agesex[i], 1, Length( dtSmall:agesex[i] ) - 1 ) ) == :age & 
		Substr( dtSmall:agesex[i], -1 ) == :sex
	);
	If( N Rows( foundRows ) > 0,
		dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
	);
);