cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Jackie_
Level VI

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

 

13 REPLIES 13
txnelson
Super User

Re: Assigning Minimum value to a column

Here is one of the examples I gave

Column( "Age" ) << Get Property( "Units" );

If you examine the documentation on the Column() function in the JSL Syntax Reference, it reads

Column(<dt>, "name", "formatted")
Column(<dt>, n)
	Description
		Gets a reference to the data table column.
	Arguments
		dt Optional reference to a data table. If this is not supplied, the current data table is used.
		name Quoted string that is the name of the column.
		formatted Quoted string that returns the formatted string of the cell value.
		n Column number.

IF you read the example from the Syntax Guide it reads:

 

The Column function can also identify a column. For Big Class.jmp, the following expressions
all refer to age, the second column in the table:
	Column( "age" );
	Column( 2 );
	Column( dt, 2 );
	Column( dt, "age" );
Jim
Jackie_
Level VI

Re: Assigning Minimum value to a column

I mean in this script replacing Column name with Column Index

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

Current Data Table( dt1 );
For( i = 1, i <= N Rows( dt2 ), i++,
	dt2:Minimum Age[i] = Col Min( If( Contains( dt2:Name[i], dt1:Name ), dt1:age, . ) )
);
Jackie_
Level VI

Re: Assigning Minimum value to a column

Hi @txnelson,

 

I tried to replace column name with column index but it's not running

Any suggestions?

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:Column(2)[i] =col min(If(contains(dt2:Column(1)[i],dt1:Column(1)),dt1:Column(2),.));
);

 

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