Column Custom Formula

pankajsync

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.

 

Id	Vg	Mod	Device	Vth
2.00E-06	3	1A	X1
2.00E-07	2.8	1A	X1
2.00E-08	2.6	1A	X1
2.00E-09	2.4	1A	X1
2.00E-10	2.2	1A	X1
2.00E-12	2	1A	X1
3.00E-06	3	1B	X1
3.00E-07	2.8	1B	X1
3.00E-08	2.6	1B	X1
3.00E-09	2.4	1B	X1
3.00E-10	2.2	1B	X1
3.00E-12	2	1B	X1
4.00E-06	3	1A	Y1
4.00E-07	2.8	1A	Y1
4.00E-08	2.6	1A	Y1
4.00E-09	2.4	1A	Y1
4.00E-10	2.2	1A	Y1
4.00E-12	2	1A	Y1
5.00E-06	3	1B	Y1
5.00E-07	2.8	1B	Y1
5.00E-08	2.6	1B	Y1
5.00E-09	2.4	1B	Y1
5.00E-10	2.2	1B	Y1
5.00E-12	2	1B	Y1

txnelson

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.  

 

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