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

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

cms — Thu, 08 Jun 2023 16:40:20 GMT

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

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

jthi — Sat, 28 Jan 2023 15:25:30 GMT

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

for a good read

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

txnelson — Sat, 28 Jan 2023 19:29:46 GMT

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

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

cms — Sun, 29 Jan 2023 04:27:28 GMT

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

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

cms — Sun, 29 Jan 2023 04:29:03 GMT

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

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

txnelson — Sun, 29 Jan 2023 06:41:57 GMT

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

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

cms — Sun, 29 Jan 2023 07:56:41 GMT

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

txnelson — Sun, 29 Jan 2023 08:21:34 GMT

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

office1 close today

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

cms — Sun, 29 Jan 2023 09:16:46 GMT

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

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

txnelson — Sun, 29 Jan 2023 10:40:01 GMT

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

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

txnelson — Sun, 29 Jan 2023 14:04:40 GMT

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

cms — Sun, 29 Jan 2023 15:23:45 GMT

thank you .appreciate your help. it worked and resolved period /colon space issue.

Found another issue if max list and max list1 are not same counts of rows it gives error due to last row being empty in maxlist1.

Maxlist	Maxlist1
office1.close:today	office2.close:today
office2.close:today	office3.close:today
office3.close:today