How to join two tables and fill automatically empty rows?

Ole_Luehn

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

Date-Category-Goto.jmp

Date_Category.jmp

WebDesignesCrow

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