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
pankajsync
Level I

Column Custom Formula

I would like to add a new column 'Vth' in my table with the following calculation:-

First I need to find the index i of column 'Id' where the 'Id' is nearest to 1e-6; and then Vth = Vg(i). It should be grouped by Mod and Device columns as well. That is, each unique combination of Mod and Device will have one Vth value.

 

IdVgModDeviceVth
2.00E-0631AX1 
2.00E-072.81AX1 
2.00E-082.61AX1 
2.00E-092.41AX1 
2.00E-102.21AX1 
2.00E-1221AX1 
3.00E-0631BX1 
3.00E-072.81BX1 
3.00E-082.61BX1 
3.00E-092.41BX1 
3.00E-102.21BX1 
3.00E-1221BX1 
4.00E-0631AY1 
4.00E-072.81AY1 
4.00E-082.61AY1 
4.00E-092.41AY1 
4.00E-102.21AY1 
4.00E-1221AY1 
5.00E-0631BY1 
5.00E-072.81BY1 
5.00E-082.61BY1 
5.00E-092.41BY1 
5.00E-102.21BY1 
5.00E-1221BY1 
3 REPLIES 3
txnelson
Super User

Re: Column Custom Formula

Here is a little script that create 2 new columns that gives you the results you asked for.  The image below has had the interim column deleted since it is only a working column.  

vth.PNG 

Names default to Here(1);

// Create the sample data table
dt=New Table( "Example",
	Add Rows( 24 ),
	New Column( "ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.000002, 0.0000002, 0.00000002, 0.000000002, 0.0000000002,
			0.000000000002, 0.000003, 0.0000003, 0.00000003, 0.000000003,
			0.0000000003, 0.000000000003, 0.000004, 0.0000004, 0.00000004,
			0.000000004, 0.0000000004, 0.000000000004, 0.000005, 0.0000005,
			0.00000005, 0.000000005, 0.0000000005, 0.000000000005]
		)
	),
	New Column( "VG",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4,
			2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2]
		)
	),
	New Column( "Mod",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B",
			"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B"}
		)
	),
	New Column( "Device",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1",
			"Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1"}
		)
	)
);

// Find the index
dt << New Column("Row", formula(
theMin = Col Minimum( Abs( 0.000001 - :ID ), :Mod, :Device );
theValue = .;
If( Abs( 0.000001 - :ID[Row()] ) == theMin,
	theValue = Row()
);));

// Set the Vth
dt<<New Column("Vth", formula(
theRow = :VG[Col Max( :Row, :Mod, :Device )]));

// Clean up
dt:Vth << delete formula;
dt<< delete columns("Row");
Jim
pankajsync
Level I

Re: Column Custom Formula

Thanks Jim. It works. How can I modify this to find the Vth which corresponds to exactly 1e-6 of ID by interpolation. I.e. finding an index before and after 1e-6 of ID and then interpolate.

index=nearest to 1e-6 (greater than 1e-6)

Interpolate( x, [VG(index) VG(index-1)  ], [log10(ID(index))  log10(ID(index-1))] );

txnelson
Super User

Re: Column Custom Formula

Here are the columns to generate what you are asking for.  I took your interpolation formula as is, and implemented it.  You may need to changes some things up.

Names default to Here(1);

// Create the sample data table
dt=New Table( "Example",
	Add Rows( 24 ),
	New Column( "ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.000002, 0.0000002, 0.00000002, 0.000000002, 0.0000000002,
			0.000000000002, 0.000003, 0.0000003, 0.00000003, 0.000000003,
			0.0000000003, 0.000000000003, 0.000004, 0.0000004, 0.00000004,
			0.000000004, 0.0000000004, 0.000000000004, 0.000005, 0.0000005,
			0.00000005, 0.000000005, 0.0000000005, 0.000000000005]
		)
	),
	New Column( "VG",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4,
			2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2]
		)
	),
	New Column( "Mod",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B",
			"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B"}
		)
	),
	New Column( "Device",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1",
			"Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1"}
		)
	)
);

// Find the index
dt << New Column("Row", formula(
theMin = Col Minimum( Abs( 0.000001 - :ID ), :Mod, :Device );
theValue = .;
If( Abs( 0.000001 - :ID[Row()] ) == theMin,
	show(.000001 - :ID[Row()]);
	theValue = Row()
);));

dt<<New Column("Interpolation Row", formula(
theRow = Col Max( :Row, :Mod, :Device );
iRow = .;
If( Row() == theRow,
	If( .000001 - :ID[theRow] > 0,
		iRow = theRow - 1,
		.000001 - :ID[theRow] < 0,
		iRow = theRow +1,
		iRow = theRow
	);
);
));
iRow;

// Set the Vth
dt<<New Column("Vth", formula(
theRow = :VG[Col Max( :Row, :Mod, :Device )]));

dt<<New Column("Exact Vth", formula(
	theRow = Col Max( :Row, :Mod, :Device );
	a=matrix(:VG[:Row[theRow]]) || matrix(:VG[:Interpolation Row[theRow]]);
	b=matrix(log10(:ID[:Row[theRow]])) || matrix(log10(:ID[:Interpolation Row[theRow]]));
	theValue=interpolate(.0000001, a, b);
	theValue;
));

// Clean up
dt:Vth << delete formula;
dt:Exact Vth << delete formula;
//dt<< delete columns("Row");
//dt<< delete columns("Interpolation Row");
Jim