nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Script - Work with Data Table from OBBC query

JRS

Hi Everyone -

I am working with two datasets stored in Microsoft Access database, which I get through ODCB queries. I need to join the two resulting data tables, before I make some more data manipulations.

I have scripted the whole process, however it doesn't not run fully at once. I always have to first run the script for the two ODCB queries, then run my script for tables joining and other manipulations.

Else I get an error message: "cannot locate data table in access or evaluation of 'data table' , Data Table/*###*/("qStreams")"

("qStreams" being of the two tables I got from ODCB query).

I feel I may need to add some script to store/declare the data tables I get from the ODCB queries in order to use it later, and this is where I am stuck, because I am very unfamiliar with JSL script (this is my first script actually).

Thanks for your help.

Accepted answers

jthi

Are you running the query in foreground or in the background (I think this is preference dependant but you can force it)? If they are run in the background it could mean that the queries do not finish before you try to join datatables together.

All comments

Hi @JRS,

Try retaining a reference to the data tables you open with each ODBC connection and using those instead of calling by name, like this:

dt1 = Open Database( "DSN=My Data Source Name", "SELECT * FROM table1" );
dt2 = Open Database( "DSN=My Data Source Name", "SELECT * FROM table2" );

dt1 << Join(
	With( dt2 ),
	...
);

JRS

Many thanks for the quick reply.

I get the concept but not able to apply it for my specific case. My query script looks like this right now:

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DBQ=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb;DefaultDir=C:\Users\E822710\Desktop\ROTF\Database For JMP;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
	),
	QueryName( "qProperties" ),
	Select(
		Column( "CaseID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamTag", "t1" ),
		Column( "StreamDescription", "t1" ),
		Column( "QualityID", "t1", Analysis Type( "Nominal" ) ),
		Column( "QualityTag", "t1" ),
		Column( "QualityDescription", "t1" ),
		Column( "Value", "t1", Alias( "StreamQualityValue" ) ),
		Column( "OriginDescription", "t1", Alias( "StreamOriginDescription" ) )
	),
	From( Table( "RW_StreamQualities", Alias( "t1" ) ) ),
	Where(
		In List(
			Column( "QualityTag", "t1" ),
			{"CRB"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	),
	Order By(
		Column( "CaseID", "t1", Order( "Ascending" ) ),
		Column( "StreamReportOrder", "t1", Order( "Ascending" ) ),
		Column( "QualityReportOrder", "t1", Order( "Ascending" ) )
	)
) << Run;

I realize I should have just shared my script right away.

Appreciate your further guidance.

Script - UnitBalance.CRB.jsl

You would set a variable equal to the value of New SQL Query. Using the highest level functions of your script, it might look like this:

//Get data
dtqStreams = New SQL Query( ... ) << Run;
dtqProperties = New SQL Query( ... ) << Run;

// Join both Data Tables
dtJoined = dtqStreams << Join( With( dtqProperties ), ... );

// Set column properties
dtJoined:"StreamFeedFlag"n << Add Column Properties( ... );

// Add new columns
dtJoined << New Column( ... );

// Make graphs from your joined data table
dtJoined << Graph Builder( ... );

JRS

Hi -

Still having some issue around joining both tables, here is the script I implemented following your post:

// Join both Data Tables
dtJoined = dtqStreams << Join(
	With( dtqProperties ),
	Merge Same Name Columns,
	Match Flag( 0 ),
	By Matching Columns( :CaseID = :CaseID, :StreamID = :StreamID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "UnitBalance.HY%" )
);

But it still gives an error:

Script - UnitBalance.CRB.jsl