topic Re: How to add column in subset tables and add value based on specific column value? in Discussions

How to create a loop and add column in subset tables and add value based on specific column value?

UserID16644 — Wed, 07 Aug 2024 01:46:25 GMT

Hi all, I have a table subset

I have two questions.

1. How can create a loop that adds column "Ref" to every subset table?

2. Also, is it possible that the value of the created column will be based on the highest number of N Rows with a specific value?

For example, the new column to be added will be Ref. Column Ref's value will be based on Column Country, wherein it should add 1 to the highest N Rows Value of America only.

Country	N Rows	Ref
Argentina	4
America	0
America	5	1
Brazil	2
Brazil	4
Canada	2

TIA

Re: How to add column in subset tables and add value based on specific column value?

txnelson — Wed, 07 Aug 2024 01:24:05 GMT

I am not sure what your definition of as "column subset" is. Is what you are referring to, the data tables created from

Tables=>Subset

when Subset By is checked

and a separate table for each level of the variable(s) selected is made?

Re: How to add column in subset tables and add value based on specific column value?

UserID16644 — Wed, 07 Aug 2024 01:43:50 GMT

After I have subset the table, how can I create a loop to add a column "Ref" on all the subsetted tables?

*Will rephrase the question

Re: How to add column in subset tables and add value based on specific column value?

txnelson — Wed, 07 Aug 2024 02:01:58 GMT

Here is an example of one way to do the looping. The real issue is how to get the list of tables to loop across. This example assumes that

Tables=>Subset

with a Subset By was used

names default to here(1);
dt =
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );

dtSubsetList = dt << Subset(
	By( :sex ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :age, :height, :weight )
);

for each( {table}, dtSubsetList,
	table << new column( "Ref");
	wait(0);
	table:ref[table << get rows where( :age == col max(:age))]=1;
);

Re: How to add column in subset tables and add value based on specific column value?

UserID16644 — Wed, 07 Aug 2024 02:23:15 GMT

What if my data looks like this? The column Country is in character type, how can I get its highest N Rows? Assuming that I am getting the highest N Rows from America. From the example you have given, I think it is in numeric data type

Country	N Rows	Ref
Argentina	4
America	0
America	5	1
Brazil	2
Brazil	4
Canada	2

Re: How to add column in subset tables and add value based on specific column value?

txnelson — Wed, 07 Aug 2024 02:46:18 GMT

I assume something similar to this will be what you need

table:ref[table << get rows where( :Country == "America" & :N Rows == col max(:N Rows, :Country))]=1;

I suggest you take the time to read the Scripting Guide, to learn JSL. If you are not familiar with programming and at least one programming language, I suggest you search the internet for a free course on "Learn how to Program".