We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
Jacksmith12
Level IV

Assigning Minimum value to a column

Hi folks,

I have 2 files.

 

1st file:

Jacksmith12_0-1585574698964.png

 

2nd file :

Jacksmith12_1-1585574737274.png

 

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:

 

Capture.PNG

 

Any suggestions please?

Thanks

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

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,.));
);
Jim

View solution in original post

Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

The Column() function is not

d2:Column(2)[I]

it is as stated

Column( dt, 2 )[I]

And if the indexes were variables,  the completed script would be

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

indexd2minage = 2;
indexd2name = 1;
indexd1name = 1;
indexd1age = 2;


Current Data Table( dt1 );
For( i = 1, i <= N Rows( dt2 ), i++,
	Column( dt2, indexd2minage )[i] =
	Col Min(
		If(
			Contains(
				Column( dt2, indexd2Name )[i],
				as Column( dt1, indexd1Name )
			),
			Column( dt1, indexd1age ),
			.
		)
	)
);
Jim

View solution in original post

13 REPLIES 13
Highlighted
Jacksmith12
Level IV

Re: Assigning Minimum value to a column

Attaching the files

Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

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,.));
);
Jim

View solution in original post

Highlighted
Jacksmith12
Level IV

Re: Assigning Minimum value to a column

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?

Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

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,.));
);
Jim
Highlighted
Jacksmith12
Level IV

Re: Assigning Minimum value to a column

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 

 

Jacksmith12_0-1585596154220.png

 

Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

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 );
);
Jim
Highlighted
Jacksmith12
Level IV

Re: Assigning Minimum value to a column

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

Highlighted
txnelson
Super User

Re: Assigning Minimum value to a column

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

 

Jim
Highlighted
Jacksmith12
Level IV

Re: Assigning Minimum value to a column

Hi @txnelson,

 

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

 

Thanks