storing variable from file and using for max function in jsl scripting

[#1]

hello members ,

am a new user just begin JSL scripting , could you provide a suggestion how to execute below need 

i have 2 files , 
File1 : contains data with thousands of columns (string) and row (numbers)

File2 : contains few list of columns which i created manually to pick interested column from file1 

my need is to do max function based on list from file 2 and create new column in file 1  

below is code which i tried and didnt work as expected.

dt = Open ("C:/File2.csv")

list = dt << get name; // list  = is basically few random column names from file1 = row 1 ,row2,row10, etc 

dt1 = Open ("C:/File1.jmp")

New column ( "max list",Numeric,Formula (Max (list)));

[#2]

Not sure how your data looks like, but here is one suggestion. Not the most simple looking solution and requires JMP16+

Names Default To Here(1);

dt = New Table("File2",
	Add Rows(0),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Format("Best", 12), Set Values({"Column 1", "Column 2"}))
);

dt1 = New Table("File1",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([2, 2, 2])),
	New Column("Column 3", Numeric, "Continuous", Format("Best", 12), Set Values([3, 3, 3]))
);

Show(dt << Get Name); // this will get name of the datatable not columns

max_list = Column(dt, "Column 1") << get values;
expr_max = Expr(Max());

For Each({col}, max_list,
	Insert Into(expr_max, NameExpr(AsColumn(Column(dt1, col))))
);
Show(NameExpr(expr_max));
Eval(EvalExpr(
	dt1 << New Column("MaxList", Numeric, Continuous, Formula(
		Expr(NameExpr(expr_max))
	));	
));

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute for a good read

[#3]

thanks for suggestion ,it didnt work as expected i might be doing something wrong. to be more clear i have provided how the file looks and requirement

file1 looks like this ( this is my main table where i would like to find max values from office1,2,4)

office1	office2	office3	office4	office 5
2	3	0	6	1
3	2	6	4	4
2	5	3	2	3
1	0	2	3	2
4	1	2	7	1

Output expected with new column Maxlist (values from office1,2,4)

simple solution : New Column("MaxList", Numeric, Continuous, Formula(Max(:"office1",:"office2,:"office4")));

office1	office2	office3	office4	office 5	..	Maxlist
2	3	0	6	1		6
3	2	6	4	4		4
2	5	3	2	3		5
1	0	2	3	2		3
4	1	2	7	1		7

since my maxlist changes daily ,i would like to use another file2 as input parameter for max list

file2:   list of column name given as input parameter and execute the script 

office1

office2

office4

looking for solution where i can give max list in file2 and execute in file1

New Column("MaxList", Numeric, Continuous, Formula(Max(Based on file2 list")));

[#4]

Here is a simple example that takes a large main table and uses lists from a second data table to create new columns in the main table that are the summation of the columns referenced in the second table.

Names Default To Here( 1 );

// Open the large data table with lots of rows and columns
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

// Create a second data table that contains a list of columns to be summed
dtSum = New Table( "Columns List",
	New Column( "Summ1", character, values( {"NPN1", "NPN2", "NPN3"} ) ),
	New Column( "Summ2", character, values( {"PNP1", "PNP2", "PNP3", "PNP4"} ) )
);
Wait(5);  // Just added a short pause so one can see the original tables before changing 

// Create the new columns
For( i = 1, i <= N Cols( dtSum ), i++,
	sumList = Column( dtSum, i ) << get values;
	dt << New Column( Column( dtSum, i ) << get name );
	Column( dt, N Cols( dt ) ) << set formula(
		Eval( Parse( "sum(" || Concat Items( sumList, "," ) || ")" ) )
	);
);

[#5]

thanks for suggestion ,my requirement is different 

file1 looks like this ( this is my main table where i would like to find max values from office1,2,4)

office1	office2	office3	office4	office 5
2	3	0	6	1
3	2	6	4	4
2	5	3	2	3
1	0	2	3	2
4	1	2	7	1

Output expected with new column Maxlist (values from office1,2,4)

simple solution : New Column("MaxList", Numeric, Continuous, Formula(Max(:"office1",:"office2,:"office4")));

office1	office2	office3	office4	office 5	..	Maxlist
2	3	0	6	1		6
3	2	6	4	4		4
2	5	3	2	3		5
1	0	2	3	2		3
4	1	2	7	1		7

since my maxlist changes daily ,i would like to use another file2 as input parameter for max list

file2:   list of column name given as input parameter and execute the script 

office1

office2

office4

looking for solution where i can give max list in file2 and execute in file1

New Column("MaxList", Numeric, Continuous, Formula(Max(Based on file2 list")));

[#6]

The code I previously provided, did exactly what you want, with one exception.  Instead of providing the Max value, it provided the Sum of the columns specified.  I have reworked the script, and changed from the Sum to the Max.  I am also using your example data tables.

My code uses the name from column in file2, as the name to be used for the new column added in the main data table.

I did this because the code allows you to specify more than one list in file2.  So that if your file2 looked like this:

It would give you the following results

Names Default To Here( 1 );

// Create the sample data tables
New Table( "Main",
	Add Rows( 5 ),
	New Column( "office1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 3, 2, 1, 4] )
	),
	New Column( "office2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [3, 2, 5, 0, 1] )
	),
	New Column( "office3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 6, 3, 2, 2] )
	),
	New Column( "office4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [6, 4, 2, 3, 7] )
	),
	New Column( "office 5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 4, 3, 2, 1] )
	)
);
New Table( "Config",
	Add Rows( 3 ),
	New Column( "Maxlist",
		Character,
		"Nominal",
		Set Values( {"office1", "office2", "office4"} )
	)
);

wait(5);  // I put in a wait just so you can see the 2 tables


// The code below this line is the that does the calculations  The code above this line would
// not normally be included
Names default to here(1); 

// point to the large data table with lots of rows and columns
dt = data table("Main");
// If you are opening the table from a folder, this statement would be
//  dt = open("the path to the table");

// point to the second data table that contains a list of columns to be summed
dtMax = data table("config");
// If you are opening the table from a folder, this statement would be
//  dtMax = open("the path to the table");

  // Just added a short pause so one can see the original tables before changing 

// Create the new columns
For( i = 1, i <= N Cols( dtMax ), i++,
	sumList = Column( dtMax, i ) << get values;
	dt << New Column( Column( dtMax, i ) << get name );
	Column( dt, N Cols( dt ) ) << set formula(
		Eval( Parse( "max(" || Concat Items( sumList, "," ) || ")" ) )
	);
);

[#7]

thank you very much txnelson ,

it worked . was doing a simple mistake of not using the column name  Maxlist in file2

But i got another error : The namespace " office1.close:today " is not defined  returns the maximum value among the argument or of the values within in the single matrix of list argument 

" office1.close:today "is one of the file2 

if i open file1 maxlist column and click edit formula the error goes off.

[#8]

Get rid of the period and colon and just use the name

office1 close today

[#9]

Could you recommend a solution which can include colon and period. As parameter list contains space colon which cannot be modified

[#10]

Please attach an example data table and parameter list, and I will take a look at a solution