topic Re: Transfer data between tables in Discussions

Transfer data between tables

StarfruitBob — Sat, 10 Jun 2023 23:58:06 GMT

Hello, I'm able to pull an SQL table that's in a very simple format. One column has a unique ID (UID) another column has column headers, and the third has a UID. I have another table where certain cells have the same UID. I want to plug the values from the first table ("UID") to the second, new table ("New").

Tabulate was the perfect tool to sort the column headers and give me a row count, but because the UID column contains both string and numeric data, I'm unable to directly populate the table with the values through tabulate, so instead I put the UID in for values in the tabulate tool. This at least gave me a way to correlate what value belongs where.

If tabulate can directly populate the actual values (no statistics needed) in place of needing to search for the UID between tables, this would be ideal.

Writing pseudocode is easy enough to show what needs to be done, but the JSL I'm not having luck with. Can anyone help?

for( i = 1, i <= N Cols( data table( "New" ) ), i++, // col-by-col
		for( j = 1, j <= N rows( data table( "New" ) ), j++, // row-by-row
			if( isempty( column( data table( "New" ), i)[j] ), continue(), // if cell empty, move to next row
				id_val = column( data table( "New" ), i)[j] &
					id_loc = data table( "UID" ) << Get rows where( :UID == id_val) &
					data table( "New" ):eval( column( data table( "New" ))[j] = data table( "UID" ):UID[ id_loc[1] ]
			);
		);
);

Thank you for your time!

Re: Transfer data between tables

ron_horne — Fri, 09 Dec 2022 23:54:08 GMT

Hi @StarfruitBob

perhaps the following can work for you.

Names Default To Here( 1 );

dtnew = New Table( "new",
	Add Rows( 3 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"1", "4", ""} ) ),
	New Column( "Column 2", Character, "Nominal", Set Values( {"", "3", ""} ) ),
	New Column( "Column 3", Character, "Nominal", Set Values( {"", "5", "2"} ) )
); 

dtuid = New Table( "uid",
	Add Rows( 5 ),
	New Column( "UID", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) ),
	New Column( "Val", Character, "Nominal", Set Values( {"apple", "12.3", "987", "abc", "3"} ) )
);

for (i=1, i<=ncols(dtnew), i++,
	for ( ii=1, ii <=nrows (dtnew), ii++,
	if ( Column (dtnew ,i)[ii] == "", continue (), 
	abc= num(  ( (Column (dtnew ,i)[ii])));
	Column (dtnew ,i)[ii] = char (dtuid:val[abc]);
	)));

the scripting feels very un natural.

let us know if it works for your application.

Re: Transfer data between tables

StarfruitBob — Sat, 10 Dec 2022 00:13:07 GMT

Hello @ron_horne ,

Column (dtnew ,i)[ii] = char (dtuid:val[abc])

The line above doesn't link the UUID found in the cells of the dt:New to the row of dt:UID that has the corresponding value in :UID and then use that row number to save into the corresponding cell in dt:New. Is there a simple command that can find a row number other than << Get rows where( :UID == abc )? If so, I think I can build off of what you answered above to complete this script.

Is there a way to make tabulate place in the actual values in the cells directly, regardless of data type & modeling, without statistics?

Re: Transfer data between tables

StarfruitBob — Mon, 12 Dec 2022 19:39:15 GMT

Solved! Instead of trying to do this with JMP, I just asked someone to modify the SQL query to do this, which is much easier.