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

## Assigning Minimum value to a column

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: Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

## 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
Highlighted

## 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
13 REPLIES 13
Highlighted

## Re: Assigning Minimum value to a column

Attaching the files

Highlighted

## 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
Highlighted

## Re: Assigning Minimum value to a column

Hi @txnelson,

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

## 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

## 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 Highlighted

## 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 ) )
);
);
dt2:Minimum Age[i] = Min( ages );
);``````
Jim
Highlighted

## Re: Assigning Minimum value to a column

Hi @txnelson ,

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

## 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

## Re: Assigning Minimum value to a column

Hi @txnelson,

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

Thanks