// join two tables with slightly different timestamps

Names Default To Here( 1 );

// simple description
description = "Join data in two tables with slightly different timestamps.";

// name of function 
myfuncname = "Fuzzymerge";

// namespace for this function.  Likely no need to change this
my_namespace = "JPL_ES";

// definition of the function
func_defn = Function( {dt_main, col_main, dt_with, col_with},
	{Default Local}, // make all variables in the function local

	// for progress indicator notes in Log
	progpercs = [.1 .2 .3 .4 .5 .6 .7 .8 .9 1.0];
	progvals = Round( N Rows( dt_main ) :* progpercs );

	// set up the kdtable object for stupid fast searching
	tvec = Column( dt_with, col_with ) << Get As Matrix;
	lookup = KDTable( tvec );

	// for quicker execution.  Make sure to end it later.
	dt_main << Begin Data Update;

	// make dummy cols for later join
	dt_main << New Column( "tmp_merge_row" );
	dt_with << New Column( "tmp_with_row_num", Formula( Row() ) );

	// column to show the delta between the columns used to match rows
	// but only add if we don't have it already (maybe from a prior FM)
	maincols = dt_main << Get Column Names( string );
	If( Contains( maincols, "Fuzzy Merge delta" ) == 0,
		dt_main << New Column( "Fuzzy Merge delta", Numeric, Continuous )
	);

	For( mainrowi = 1, mainrowi <= N Rows( dt_main ), mainrowi++, 
		// progress note
		progind = Contains( progvals, mainrowi );
		If( progind != 0,
			Write( "Fuzzy Merge: " || Char( Eval( progpercs[progind] * 100 ) ) || "% done\!N" )
		);

		// get closest row in the 'with' table
		myval = Matrix( Column( dt_main, col_main )[mainrowi] );
		{withrownum, dist} = lookup << K nearest rows( {1, 1}, myval );

		// add this row to the list of rows to 'merge'
		dt_main:tmp_merge_row[mainrowi] = withrownum;

		// remember the delta between the two cols
		dt_main:Fuzzy Merge delta[mainrowi] = dist;
	);

	// merge in the data from the 'with' table.
	dt_main << Update( With( dt_with ), Match Columns( :tmp_merge_row = :tmp_with_row_num ), Replace columns in main table(NONE) );

	// force the table to be compressed on save
	dt_main << Compress File When Saved( 1 );

	// cleanup the tmp columns
	dt_main << Delete Columns( "tmp_merge_row" );
	dt_with << Delete Columns( "tmp_with_row_num" );

	dt_main << End Data Update;
			
	Return( dt_main );
);

// add the function to the workspace
newAdd = New Custom Function( my_namespace, myfuncname, func_defn );
newAdd << Description( description );
newAdd << Prototype( Eval( my_namespace || ":" || myfuncname || "(dt_main, col_main, dt_with, col_with)" ) );
newadd << Example(
	Eval( my_namespace || ":" || myfuncname || "(\!"my main table\!", \!"main time col\!", \!"my with table\!", \!"timevec_with\!")" )
);
newAdd << Formula Category( "JPL_Stuff" );
Add Custom Functions( newAdd );