How to join two tables and fill automatically empty rows?

Ole_Luehn — Thu, 26 Sep 2024 07:54:44 GMT

Dear.

I am trying to auto-join a table but I failed so fare.

My table shows a date with category which is - in the date - increased by hours.

In addition, it is showing a category - A-B-C-D. This table is called date-category.

Now, I need a table which is showing date and hours and MINUTES increased.

I attached a second table. (date-category-goto).

Can someone join the category to my goto-table? (and put a script?)

I want to extract in the next step my factors and responses from our process control system, and see

which "category" makes the most changes to the process....

Best wishes.

Ole

Re: How to join two tables and fill automatically empty rows?

WebDesignesCrow — Thu, 26 Sep 2024 08:48:44 GMT

Hi @Ole_Luehn ,

I'm able to join the two table table & able to find the JSL code to fill & replace missing with previous value (this can be done through GUI too).

// Open Data Table: Date_Category.jmp
// → Data Table( "Date_Category" )
Open( "$DESKTOP/Data/Date_Category.jmp" );

// Open Data Table: Date-Category-Goto.jmp
// → Data Table( "Date-Category-Goto" )
Open( "$DESKTOP/Data/Date-Category-Goto.jmp" );

// Change column info: Date
Data Table( "Date_Category" ):Date << Data Type( Character ) <<
Set Modeling Type( "Nominal" );

// Change column info: Date-goto
Data Table( "Date-Category-Goto" ):"Date-goto"n << Data Type( Character ) <<
Set Modeling Type( "Nominal" );

// Change column name: Category → Category1
Data Table( "Date_Category" ):Category << Set Name( "Category1" );

// Join data tables
// → Data Table( "Test" )
Data Table( "Date-Category-Goto" ) << Join(
	With( Data Table( "Date_Category" ) ),
	Select( :"Date-goto"n ),
	SelectWith( :Category1 ),
	By Matching Columns( :"Date-goto"n = :Date ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Test.jmp" )
);


//Fill in empty rows
Names Default To Here( 1 );
dt = Current Data Table();

dtcollist = dt << get column names;

dtMat = (dt << Get All Columns As Matrix);

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = column(selection[i]);
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);

Pls refer to link below too since I can't really explain how the fill-in script works ;

https://community.jmp.com/t5/Discussions/How-to-replace-empty-rows-until-next-entry-and-continue/td-p/269171

https://community.jmp.com/t5/JMP-Add-Ins/Fill-Missing-Cells/ta-p/460326

Re: How to join two tables and fill automatically empty rows?

Ole_Luehn — Thu, 26 Sep 2024 11:55:10 GMT

Hi. It worked - I downloaded this Add-In - and updated my merged file.

Thank you - now I can continue.
Best wishes.
Ole

topic How to join two tables and fill automatically empty rows? in Discussions

How to join two tables and fill automatically empty rows?

Re: How to join two tables and fill automatically empty rows?

Re: How to join two tables and fill automatically empty rows?