Assigning Minimum value to a column

[#1]

Hi folks,

I have 2 files.

1st file:

2nd file :

I am trying to write a script which will search for the Minimum age from the 1st file and assign the lowest age in the 2nd file 

For e.g.

The 1st row which has three name Sam, John, Smith so the Value in the Minimum Age Column should be Min{Sam, John, Smith}  Min{21, 27, 32} = 21

my final output will look something like this:

Any suggestions please?

Thanks

[#2]

Attaching the files

[#3 · Accepted solution]

Here is a short script that will do what you want......the key is that most individuals do not realize that what is passed to a calculation function, can be conditional.  See the script below

Names default to here(1);
dt1=data table("1st");
dt2=data table("2nd file");

current data table(dt1);
For(i=1,i<=nrows(dt2), i++,
	dt2:Minimum Age[i] =col min(If(contains(dt2:Name[i],dt1:Name),dt1:age,.));
);

[#4]

Hi @txnelson,

Thanks for your help. 

2nd file doesn't have Minimum Age Column. How can I create the 2nd column with name "Minimum Age" and then assign the lowest value?

[#5]

What kept you from being able to take the example from "Adding new columns to a data table " and add it to your script?

Did you know that you can go to the Scripting Guide, and get examples of how to do things?  If not, I very strongly recommend that you familiarize yourself with it.

Here is the sample script I gave you, with the new command to add the new column

Names default to here(1);
dt1=data table("1st");
dt2=data table("2nd file");

dt2 << New Column( "Minimum Age" );

current data table(dt1);
For(i=1,i<=nrows(dt2), i++,
	dt2:Minimum Age[i] =col min(If(contains(dt2:Name[i],dt1:Name),dt1:age,.));
);

[#6]

Hi @txnelson ,

What if the Age values are stored in column properties? How can I import column properties (Age) of 1st_file into the column (Minimum Age) of 2nd file 

[#7]

First, I need to clarify some terminology.

1.  In data table 1st_file, you have 9 columns
2. In data table 1st_file you have 1 row of data for each column.  You do not have what JMP refers to as column properties.  If you right click on a column header, and select "Column Properties", you will see an extensive list of items that refer to the particular column that you selected.  

I mention the above, only because when you used the keywords, Column Property, I immediately went to the idea of JMP Column Properties.  That setup a bunch of false assumptions which lead to a bunch of confusion for me.

That aside, here are a couple the large number of ways one can solve you issue

Names Default To Here( 1 );
dt1 = Data Table( "1st_file" );
dt2 = Data Table( "2nd file" );

dt2 << New Column( "Minimum Age" );

// Transpose the data into a standard data table format
dt3 = dt1 << Transpose(
	invisible,
	columns( :Sam, :Andy, :John, :Ed, :Jonny, :Smith, :Scott, :George, :Mathew ),
	Output Table( "Transpose of 1st_file" )
);


Current Data Table( dt3 );
For( i = 1, i <= N Rows( dt2 ), i++,
	dt2:Minimum Age[i] = Col Min( If( Contains( dt2:Name[i], dt3:Label ), dt3:Name( "Row 1" ), . ) );
	Show( dt2:minimum Age[i] );
);

Close( dt3, nosave );

Take a little more time in processing the data, but a secondary table creation isn''t required.

Names Default To Here( 1 );
dt1 = Data Table( "1st_file" );
dt2 = Data Table( "2nd file" );

dt2 << New Column( "Minimum Age" );

For( i = 1, i <= N Rows( dt2 ), i++,
	ages = {};
	For( k = 1, k <= N Cols( dt1 ), k++,
		If( Contains( dt2:Name[i], (Column( dt1, k ) << get name) ) > 0,
			Insert Into( ages, Column( dt1, k )[1] )
		);
	);
	dt2:Minimum Age[i] = Min( ages );
);

[#8]

Hi @txnelson ,

Thanks for your quick response. 

I didn't clarified my question. 

1.In data table 1st_file, you have 9 columns Yes
2. In data table 1st_file you have 1 row of data for each column.

If you right click on a column header, and select "Column Properties", you will see an extensive list of items that refer to the particular column that you selected.

Can I import these extensive list values into column of 2nd_file?

Can we use the syntax "Get Property()" 

For e.g.

dt1 << Get Property("Age");

and then use for loop to assign it in the 2nd file?

Thanks again

[#9]

The syntax for get property is

obj << Get Property( the property you want to retrive );

Therefore you would ask for one of the Column Properties for the column Age as:

:Age << Get Property( "Spec Limits" );
// or
Column( "Age" ) << Get Property( "Units" );

And JMP allows one to create their own Column Properties, so you could do something like

dt1:Sam << Set Property("Age", 3);
dt1:Andy << Set Property("Age", 4);
theAge = dt1:Andy << Get Property( "Age" );

[#10]

Hi @txnelson,

How can I define Column index instead of Column name in the above script?

Thanks