topic Re: Summarize function Date in Discussions

Summarize function Date

dhurw — Sat, 10 Jun 2023 23:24:25 GMT

I pretty new to jmp and have been trying to gain as much knowledge as possible on the the scripting language. I have been referencing both the scripting guide and the index if I run into any issues. As far as the Summarize function is concerned, is there somethin I am missing.... I am attempting to pull the min and max date and I am getting a rather large numerical value.

As far as the formatting is concerned this is what I have..

Summarize(
	pNum = By( :Part Number ), 

	minL = Min( :Start Length ),
	maxL = Max( :Start Length ),
	massM = Mean( :Name( "Mass (g/m2)" ) ),
	sDevM = Std Dev( :Name( "Mass (g/m2)" ) ),
	minT = Min( :Time ) ,
	maxT = Max( :Time ) 
);

Re: Summarize function Date

txnelson — Mon, 28 Dec 2020 21:41:16 GMT

JMP DateTime variables are numeric values based upon the number of seconds since January 1, 1904. Therefore the actual numeric value is a very large number. To display the value as a time value, the easiest way, is to specify a format() function.

Summarize(
	pNum = By( :Part Number ), 

	minL = Min( :Start Length ),
	maxL = Max( :Start Length ),
	massM = Mean( :Name( "Mass (g/m2)" ) ),
	sDevM = Std Dev( :Name( "Mass (g/m2)" ) ),
	minT = Min( :Time ) ,
	maxT = Max( :Time ) 
);

show( format(minT, "m/d/y h:m:s"),format(maxT, "m/d/y h:m:s"));

Re: Summarize function Date

dhurw — Tue, 29 Dec 2020 13:49:37 GMT

Unfortunately, that is not working for this script. The original script was not made by me, although I assume this is the culprit.

Column( SqlQry1, "DateTime" ) << data type( "Numeric" );
SqlQry1 << New Column( "Time", formula( Round( :DateTime, 0 ) ) );

Column( SqlQry1, "Time" ) << Format( "m/d/y h:m:s" ) << delete formula;
//Column( SqlQry1, "Time")  << Modeling Type( "Nominal");
SqlQry1 << delete column( "DateTime" );
dt0 = SqlQry1 << split( split by( :TagName ), Split( :vValue ), group( :Time ) );
Close( SqlQry1, no save );

Re: Summarize function Date

txnelson — Tue, 29 Dec 2020 15:31:51 GMT

what do you mean when you say it isn't working? Without having an actual sample of your data table, I am not seeing anything of issue.

Re: Summarize function Date

dhurw — Tue, 29 Dec 2020 17:07:10 GMT

I am getting this error...

Which to me doesn't make sense, in that the column was created utilizing the previous script as a numeric time column.

The data is being pulled from an SQL server.

Re: Summarize function Date

txnelson — Tue, 29 Dec 2020 17:20:40 GMT

place a Num() function around the minT and see what happens. I agree, minT should be numeric

When you move your cursor over minT what value is displayed?

Re: Summarize function Date

dhurw — Tue, 29 Dec 2020 17:24:19 GMT

This is the alert I am getting...

Not sure how it could be an expression or a row state...

Re: Summarize function Date

txnelson — Mon, 04 Jan 2021 16:19:43 GMT

Can you please supply more of your script. Preferably from the Summarize to the point where you are doing the

Show( Format( Num( minT ), "m/d/y h:m:s" ) )

Re: Summarize function Date

dhurw — Tue, 29 Dec 2020 19:13:22 GMT

Here is from when the time column was created in a data table to the calling of show()

Column( SqlQry1, "DateTime" ) << data type( "Numeric" );
SqlQry1 << New Column( "Time", formula( Round( :DateTime, 0 ) ) );

Column( SqlQry1, "Time" ) << Format( "m/d/y h:m:s" ) << delete formula;
//Column( SqlQry1, "Time")  << Modeling Type( "Nominal");
SqlQry1 << delete column( "DateTime" );
dt0 = SqlQry1 << split( split by( :TagName ), Split( :vValue ), group( :Time ) );
Close( SqlQry1, no save );

//move everything that's not a mass/position to the beginning such that we can stack all the other columns at the end.
dt0 << Move Selected Columns(
	{"Data", "Data", "Data",
	"Data", "Data", "Data",
	"Data", "Data"},
	after( "Time" )
);



//duplicate columns so we can downfill some without modifying the originals. since we dont have roll begin/end markers we may need the originals
dt0 << New Column( "Part Number",
	after( :Time ),
	"character",
	"nominal",
	formula( :Data )
) << MoveSelectedColumns( {:Part Number}, After( :Data ) );
dt0:Part Number << delete formula;

dt0 << New Column( "Lot Number", "character", "nominal", formula( :Data ) ) <<
MoveSelectedColumns( {:Lot Number}, After( :Data ) );
dt0:Lot Number << delete formula;
	
dt0 << New Column( "Split",
	after( :Data ),
	"Numeric",
	"nominal",
	formula( :Data )
) << MoveSelectedColumns( {:Split}, After( :Data ) );
dt0:Split << delete formula;

//This down fills the part number tag so that it can be associated with each roll	
For( i = 1, i <= N Rows( dt0 ), i++, 
	/*If( Is Missing( :Part Number[i] ),
		:Part Number[i] = Part Number[i - 1]);*/
		
	Try(
		If( Is Missing( :Lot Number[i] ),
			:Lot Number[i] = Lot Number[i - 1]
		);
	
		If( Is Missing( :Part Number[i] ),
			:Part Number[i] = Part Number[i - 1]
		);
	
		If( Is Missing( :Split[i] ),
			:Split[i] = Split[i - 1]
		);
	)
);

//This makes all the mass, position columns numeric but not the part/lot # ones
For( i = 9, i <= N Col(), i++,
	Column( i ) << data type( numeric ) << modeling type( "Continuous" )
);

//assign each mass point a CW position	
For( i = 0, i < 512, i++,
	x = "Position Lane " || Char( i );
	dt0 << New Column( x,
		formula( :Data+ (:Data) * (i + 1) )
	);
	Column( dt0, x ) << delete formula;
); 

//form a list of all the mass and position columns without having to use 1000 lines of code
col_list = dt0 << get column names( string );
For( i = N Items( col_list ), i > 0, i--,
	If(
		(!Contains( col_list[i], "Data" )) & (!
		Contains( col_list[i], "Position Lane" )),
		Remove From( col_list, i )
	)
);


//Stack the mass and position columns	
dt1 = dt0 << Stack(
	columns( col_list ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Number of Series( 2 ),
	Contiguous
);

Close( dt0, no save );

//renames the appropriate columns as mass / positions
:Data << Set Name( "Mass (g/m2)" );
:Data 2 << Set Name( "Position (cm)" );

dt1 << delete column( "Label" );
dt1 << delete column( "Label 2" );

dt1 << New Script(
	"CW Graph",
	Graph Builder(
		Size( 522, 571 ),
		Show Control Panel( 0 ),
		Variables( X( :Name( "Position (cm)" ) ), Y( :Name( "Data" ) ) ),
		Elements( Smoother( X, Y, Legend( 6 ) ) ),
		Local Data Filter(
			Add Filter(
				columns( :Lot Number ),
				Display( :Lot Number, Size( 178, 276 ), Height( 276 ) )
			)
		)
	)
);

dt1 << set name( "Data" );

dt1 << Delete Table Property( "Source" );
dt1 << Delete Table Property( "Update from DB" );
dt1 << Delete Table Property( "Save To DB" ); 

dt1 << New Column( "Lanes",
	numeric,
	continuous,
	formula( 10 + 10 * Floor( (:Name( "Position (cm)" ) - 10) / 10 ) )
);
Column( dt1, "Mach.StartLength" ) << set name( "Start Length" );
//Deleting empty Lane rows
dt1 << Select Where( Is Missing( :Name( "Lanes" ) ) ) << Delete Rows;
//Variables for the Summary Stats
pNumLot = {:Part Number, :Lot Number};
Summarize(
	pNum = By( :Part Number ), 
	//lNum = By( :Lot Number ), //this needs to be added
	//pLNum = By( Eval( pNumLot ) ),
	minL = Min( :Start Length ),
	maxL = Max( :Start Length ),
	massM = Mean( :Name( "Mass (g/m2)" ) ),
	sDevM = Std Dev( :Name( "Mass (g/m2)" ) ),
	minT = Min( :Time ),
	maxT = Max( :Time )
);
Show( Format( minT, "m/d/y h:m:s" ), Format( maxT, "m/d/y h:m:s" ) );

Re: Summarize function Date

txnelson — Tue, 29 Dec 2020 22:23:51 GMT

I should have realized this earlier. Because the Summarize() has a by group, the values of minT and maxT are matrices. If you change the Show() line to

Show( Format( minT[1], "m/d/y h:m:s" ), Format( maxT[1], "m/d/y h:m:s" ) );

you should see the min and max times for the first Part Number