386960_en-US

liliconcom

Script - Work with Data Table from OBBC query

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.

 

ScriptingRe: Script - Work with Data Table from OBBC query

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 ),
 ...
);

Re: Script - Work with Data Table from OBBC query

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.

Re: Script - Work with Data Table from OBBC query

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( ... );

 

Re: Script - Work with Data Table from OBBC query

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: