Virtual Join via JSL does not work

[#1]

Dear JMP Community,

I'm linking two data tables via JSL for a virtual join:

dt1:Link_Column<< Set Property( "Link ID", 1 );
dt2:Link_Column << Set Property( "Link Reference", Reference table(dt1 ));

I see the key symbols in both tables and in the Column Info, so the linking seems to work, but the virtually joined columns do not appear in my joined data table.

If I do exactly the same thing by hand and clicking in JMP, it works and the joined columns appear.

Is there a way to force an update after virtually joining?

[#2]

I am only guessing for a quick reply but did you compare the resulting definition of the column property for dt2:LinkColumn when you successfully, interactively defined it versus when you unsuccessfully scripted it?

I am concerned that the Reference Table argument expects a literal argument, not a variable. See this Help entry about scripting a virtual join.

[#3]

Also, it may help to save the tables before joining.

[#4 · Accepted solution]

Here's an example, that should work.

It queries two tables from JMP sample tables, and joins them afterwards.

As others mentioned, there may be some issues for troubleshooting, probably you can find something in the log

• the referenced table needs to be saved
• the referenced table needs to have no duplicates
• are the references (table name, column name) correct?
• ...

The keys on both sides needs to be saturated with color, and not pale. Pale means not working.

I had some trouble with it, too, but it works.

// User Dialog for query from datatable (database)

/***************************************************************************/
// Initialization 
/***************************************************************************/
Names Default To Here( 1 );
Clear Log();

// Query 1st table with filter conditions
dt_filter = New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables( ["Big Class" => "\C:\Program Files\SAS\JMPPRO\15\Samples\Data\Big Class.jmp"] ),
	QueryName( "filter" ),
	CustomSQL( "SELECT distinct t1.name, t1.age, t1.sex, t1.weight, t1.height FROM \!"Big Class\!"  t1 where t1.sex = 'M' and t1.age<=13;" )
) << Run;

Summarize( name_lst = by( dt_filter:name ) );
dt_filter:name << set property("Link ID", 1);
dt_filter << save("$TEMP\dt_filter.jmp");

// (2) assign custom SQL, 
Assign(
	custom_sql,
	"\[
SELECT t1.picture, t1.name, t1."sibling ages", 
	t1.sports, t1."countries visited", t1."family cars", t1."reported illnesses", 
	t1."age vector" 
FROM "Big Class Families"  t1 
where t1.name in __NAMELIST__
;
]\"
);

// (3) Put namelist into custom_sql
Substitute Into( custom_sql, "__NAMELIST__", Substitute( Char( name_lst ), "{", "(", "}", ")" ) );

// (4) execute Query
dt_result = New SQL Query( Version( 130 ), Connection( "JMP" ),
	, JMP Tables( ["Big Class Families" => "\C:\Program Files\SAS\JMPPRO\15\Samples\Data\Big Class Families.jmp"] ),
	 QueryName( "dt_result" ), CustomSQL( custom_sql ) )
 << Run;
dt_result << save("$TEMP\dt_result.jmp");

// (5) Connect both tables by symbolic link
dt_result:name << set property("Link Reference", Reference Table("$TEMP\dt_filter.jmp"));
 

[#5]

The secret was a) saving both data tables and b) Set Property( "Link Reference", Reference table() has to be a filename reference, as mentioned in the replies.

Not sure why, but I'll take it...

[#6]

Great!

in Reverse Geocode Lat-Lon to Zip I had to save the files too; you can use the handle rather than the disk file name, but the disk file does need to exist.

1		blobdbf = za << read( "cb_2019_us_zcta510_500k.dbf", Format( blob ) );
2		tempfile = Save Text File( "$temp/cb_2019_us_zcta510_500k.dbf", blobdbf );
3		zipcodeNS:dtdbf = Open( tempfile, Add to Recent Files( 0 ), invisible );
4		zipcodeNS:dtdbf << save( "$desktop/cb_2019_us_zcta510_500kdbf.jmp" );
5	, //
6		zipcodeNS:dtshp = Open( "$desktop/cb_2019_us_zcta510_500kshp.jmp", Add to Recent Files( 0 ), invisible );
7		zipcodeNS:dtdbf = Open( "$desktop/cb_2019_us_zcta510_500kdbf.jmp", Add to Recent Files( 0 ), invisible );
8	);
9	zipcodeNS:dtdbf:shape << Set Property( "Link ID", 1 );
10	zipcodeNS:dtshp:shape << Set Property( "Link Reference", Reference Table( zipcodeNS:dtdbf ) );

Line 3 is opening a non-JMP file as a table.

Line 4 saves the table as a JMP file, or line 7 re-opens from disk.

Line 10 creates the link reference from the dtdbf handle without explicitly using the $desktop name.

The dtshp file could be re-saved with the link in place, but I did not do that. That would be helpful if the file was used outside of this script.

[#7 · Accepted solution]

dt1 = Open( "$SAMPLE_DATA\Pizza Profiles.jmp" );
		dt2 = Open( "$SAMPLE_DATA\Pizza Responses.jmp" );

		dt1:ID << Set Property( "Link ID", 1 );

		dt2:Choice << Set Property( "Link Reference", Reference Table( dt1 ) );
		dt2:Choice1 << Set Property( "Link Reference", Reference Table( dt1 ) );
		dt2:Choice2 << Set Property( "Link Reference", Reference Table( dt1 ) );

		dist1 = dt2 << Distribution(
			Nominal Distribution( Column( Referenced Column( "Cheese[Choice]", Reference( Column( :Choice ), Reference( Column( :Cheese ) ) ) ) ) ),
			Nominal Distribution( Column( Referenced Column( "Cheese[Choice1]", Reference( Column( :Choice1 ), Reference( Column( :Cheese ) ) ) ) ) ),
			Nominal Distribution( Column( Referenced Column( "Cheese[Choice2]", Reference( Column( :Choice2 ), Reference( Column( :Cheese ) ) ) ) ) )
		);
		

here is an example that will work -- these are sample data tables, using the dt1, dt2 scripting concept. A good practice to ensure it always works is to do what is suggested above and always save the tables before creating the links. In this example, since these are samples there is not a save in here.

[#8]

Now I am a bit confused - so the big issue why the virtual join does not work in JSL is that it is not saved?

Is there a technical reason for this? - virtual linking works for unsaved data tables when I do it by hand, but not in JSL? Are both doing different things?

My dt1 was created by importing data and manipulating it - why is the variable dt1 different if I use, e.g. the json import wizard to create it, than by using the Open() on an existing data table when both point to an open data table in JMP?

[#9]

Interesting question.

Is saving of the table "just" needed to get a unique file name for the link?
would it be possible to use the datatable name instead (without the full path)?

In this example, to link the reference table (dt2) properly to dt1, it has to be saved first

- for dt3 it's fine without saving.

Without saving any of the tables, sometimes it helped for me just to remove the Link ID (generated by JSL script) from the reference table and add it again manually.

If jmp behaviour is kept like this, would it be possible to show a warning message like "linking to unsaved data tables doesn't work"

// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );


// Data table summary
// → Data Table( "Big Class By (sex)" )
dt 2 = Data Table( "Big Class" ) << Summary(
	Group( :sex ),
	Median( :height ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);


// Change column link ID: sex
dt2:sex << Set Property( "Link ID", 1 );

// uncomment this line to make the virtual link work table d1 
//dt2 << save( "$desktop/summaryTable.jmp" );


// Change column link reference: sex
dt1:sex << Set Property(
	"Link Reference",
	Reference Table( dt2 )
);

dt3 = Eval(dt1 << getScript);
dt3:sex << Set Property(
	"Link Reference",
	Reference Table( dt2 )
);

[#10]

Thank you @Feli and @hogi for investigating on this.

The key question here is:

How to create a working link between two unsaved data tables via a single script?

It is actually possible with two different scripts, but not with one single script: https://community.jmp.com/t5/Discussions/Setting-link-ID-in-jsl-seems-not-to-work-what-is-wrong/m-p/...

The main takeaway from this thread is "they must be saved".

Follow-up question:

Why is it possible to link to unsaved tables via clicking but not via one single script?

Is this issue (i think it is a bug) addressed and on the table somewhere? For me the initial question by @Feli is not resolved.