594422_en-US

liliconcom

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

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

 

 

 

 

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

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

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

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.

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

I have taken a second look at your latest post, and I believe that I have a new script that will take care of the complex name issue that you exposed in that post.

// 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 );
	nameList = ":\!"" || sumList[1] || "\!"n";
	For( k = 2, k <= N Rows( dtMax ), k++,
		Insert Into( nameList, ", :\!"" || sumList[k] || "\!"n" )
	);
	Column( dt, N Cols( dt ) ) << set formula( Eval( Parse( "max(" || nameList || ")" ) ) );
);

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

 

 

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