topic Re: Join Bedingungen in JSL übertragen in Discussions

Join Bedingungen in JSL übertragen

markus — Fri, 09 Jun 2023 22:07:32 GMT

Ich habe mit JMP Query Builder ein Skript erstellt, das 2 Tabellen zusammenführen soll. Das funktioniert auch gut. Jedoch wenn ich das Skript modifiziere, verliert es den ursprünglichen Bezug, was Tabelle 1 und was 2 war.

Das Problem ist Zeile 209 im Skript. Es bleibt an dieser Stelle immer hängen und findet die beiden Tabellen nicht.

Names Default To Here( 1 );

//Select file: 1st: Adj & 2nd: Align

myAdj = Pick File(
"Select JMP File",
"...\Adj.xlsx"
);

myAlign = Pick File(
"Select JMP File",
"Align.xlsx");

// Open xlsx Files and import to JMP

exAdj = Open(
 myAdj, 
 invisible,
 Worksheets( "Tabelle1" ),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 1 ),
  Data Starts on Row( 2 ),
  Data Starts on Column( 1 ),
  Data Ends on Row( 0 ),
  Data Ends on Column( 0 ),
  Replicated Spanned Rows( 1 ),
  Replicated Spanned Headers( 0 ),
  Suppress Hidden Rows( 1 ),
  Suppress Hidden Columns( 1 ),
  Suppress Empty Columns( 1 ),
  Treat as Hierarchy( 0 ),
  Multiple Series Stack( 0 ),
  Import Cell Colors( 0 ),
  Limit Column Detect( 0 ),
  Column Separator String( "-" ),
  Column Numeric Format( Index( 0 ), Column Name( ID ), Format( "Best", 9 ) )
 )
);
exAdj << new column("TestRun_max", "numeric", "contiuous", Formula(If( :TestRun == Col Maximum( :TestRun, :ProductionNumber ),
 1,
 0
) ));
exAdj:SerialNumber << Set Data Type( "Character" );
exAdj:SerialNumber << set modeling type ("nominal");
exAdj:ProductionNumber << Set Data Type( "Character" );
exAdj:ProductionNumber << set modeling type ("nominal");
exAdj << select where( :TestRun_max != 1);
exAdj << wait();
exAdj << delete rows;
exAdj << select where(is missing(:TestRun));
exAdj << wait();
exAdj << delete rows;
exAdj << set name("Adj");

exAlign = Open(
 myAlign, 
 invisible,
 Worksheets( "Tabelle1" ),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 1 ),
  Data Starts on Row( 2 ),
  Data Starts on Column( 1 ),
  Data Ends on Row( 0 ),
  Data Ends on Column( 0 ),
  Replicated Spanned Rows( 1 ),
  Replicated Spanned Headers( 0 ),
  Suppress Hidden Rows( 1 ),
  Suppress Hidden Columns( 1 ),
  Suppress Empty Columns( 1 ),
  Treat as Hierarchy( 0 ),
  Multiple Series Stack( 0 ),
  Import Cell Colors( 0 ),
  Limit Column Detect( 0 ),
  Column Separator String( "-" ),
  Column Numeric Format( Index( 0 ), Column Name( ID ), Format( "Best", 9 ) )
 )
);
exAlign << new column("TestRun_max", "numeric", "contiuous", Formula(If( :TestRun == Col Maximum( :TestRun, :ProductionNumber ),
 1,
 0
) ));
exAlign:SerialNumber << Set Data Type( "Character" );
exAlign:SerialNumber << set modeling type ("nominal");
exAlign:ProductionNumber << Set Data Type( "Character" );
exAlign:ProductionNumber << set modeling type ("nominal");
exAlign << select where( :TestRun_max != 1);
exAlign << wait();
exAlign << delete rows;
exAlign << select where(is missing(:TestRun));
exAlign << wait();
exAlign << delete rows;
exAlign << set name("Align");

New SQL Query(
 Version( 130 ),
 Connection( "JMP" ),
 JMP Tables(
  exAlign,
  exAdj
 ),
 QueryName( "OptAdj-Align" ),
 Select(
  Column( "SerialNumber", "t1", Alias( "SerialNumber Allign" ) ),
  Column( "SerialNumber", "t2", Alias( "SerialNumber Adj" ) ),
  Column( "ProductionNumber", "t1", Alias( "ProductionNumber Allign" ) ),
  Column( "ProductionNumber", "t2", Alias( "ProductionNumber Adj" ) ),
  Column( "Freipruefung", "t1", Alias( "Freipruefung Allign" ) ),
  Column( "Freipruefung", "t2", Alias( "Freipruefung Adj" ) ),
  Column(
   "TestRun",
   "t1",
   Alias( "TestRun Allign" ),
   Analysis Type( "Ordinal" )
  ),
  Column(
   "TestRun",
   "t2",
   Alias( "TestRun Adj" ),
   Analysis Type( "Ordinal" )
  ),
  Column( "TestState", "t1", Alias( "TestState Allign" ) ),
  Column( "TestState", "t2", Alias( "TestState Adj" ) ),
  Column( "ErrorNumber", "t1", Alias( "ErrorNumber Allign" ) ),
  Column( "ErrorUserText", "t1", Alias( "ErrorUserText Allign" ) ),
  Column(
   "ErrorNumber",
   "t2",
   Alias( "ErrorNumber Adj" ),
   Analysis Type( "Nominal" )
  ),
  Column( "Mode", "t1" ),
  Column( "Mode", "t2", Alias( "Mode Adj" ), Analysis Type( "Nominal" ) ),
  Column( "WUserName", "t2", Alias( "WUserName Adj" ) ),
  Column( "WUserName", "t1", Alias( "WUserName Allign" ) ),
  Column(
   "DateOfTest",
   "t1",
   Alias( "DateOfTest Allign" ),
   Numeric Format( "d.m.y h:m:s", "0", "NO", "" )
  ),
  Column(
   "Month Year[DateOfTest]",
   "t1",
   Alias( "Month Year[DateOfTest] Allign" ),
   Analysis Type( "Ordinal" ),
   Numeric Format( "m.y", "-1", "NO", "" )
  ),
  Column(
   "Month[DateOfTest]",
   "t1",
   Alias( "Month[DateOfTest] Allign" ),
   Analysis Type( "Ordinal" )
  ),
  Column(
   "Year Week[DateOfTest]",
   "t1",
   Alias( "Year Week[DateOfTest] Allign" )
  ),
  Column(
   "Date[DateOfTest]",
   "t1",
   Alias( "Date[DateOfTest] Allign" ),
   Numeric Format( "d.m.y", "-1", "NO", "" )
  ),
  Column(
   "DateOfTest",
   "t2",
   Alias( "DateOfTest Adj" ),
   Numeric Format( "d.m.y h:m:s", "0", "NO", "" )
  ),
  Column(
   "Date[DateOfTest]",
   "t2",
   Alias( "Date[DateOfTest] Adj" ),
   Numeric Format( "d.m.y", "-1", "NO", "" )
  ),
  Column(
   "Month Year[DateOfTest]",
   "t2",
   Alias( "Month Year[DateOfTest] Adj" ),
   Analysis Type( "Ordinal" ),
   Numeric Format( "m.y", "-1", "NO", "" )
  ),
  Column(
   "Year Week[DateOfTest]",
   "t2",
   Alias( "Year Week[DateOfTest] Adj" )
  ),
  Column( "OptimalNeuMaß", "t1" ),
  Column( "Y_Deviation_Sensor0", "t2" ),
  Column( "Y_Deviation_Sensor1", "t2" ),
  Column( "Y_Deviation_Sensor3", "t2" ),
  Column( "Y_Deviation_Sensor4", "t2" ),
  Column( "Y_Deviation_Sensor5", "t2" )
 ),
 From(
  Table( "Adj", Alias( "t1" ) ),
  Table( "Align", Alias( "t2" ),
   Join(
    Type( Inner ),
    EQ( Column( "SerialNumber", "t1" ), Column( "SerialNumber", "t2" ) )
     & EQ(
     Column( "ProductionNumber", "t1" ),
     Column( "ProductionNumber", "t2" )
    )
   )
  )
 ),
 PostQueryScript(
  "dt = current data table();
dt << Row Selection( Select where( :SerialNumber Allign < 500000 ) );
dt << delete rows;"
 )
) << run;

close(exAlign);
close(exAdj);

Wo liegt der Fehler?

Danke für eure Hilfe im Voraus!

Markus

Re: Join Bedingungen in JSL übertragen

ih — Fri, 12 Mar 2021 13:48:11 GMT

Hi @markus,

You store references to both tables as exAdj and exAlign, I think you want to re-use those references in the lines that are throwing errors. That part of the code could look something like this:

From(
		Table( exAdj, Alias( "t1" ) ),
		Table(
			exAlign,
			Alias( "t2" ),
			Join(
				Type( Inner ),
				EQ( Column( "SerialNumber", "t1" ), Column( "SerialNumber", "t2" ) ) & EQ(
					Column( "ProductionNumber", "t1" ),
					Column( "ProductionNumber", "t2" )
				)
			)
		)
	),

Re: Join Bedingungen in JSL übertragen

markus — Tue, 16 Mar 2021 07:58:38 GMT

Thanks IH, for your answer!

I changed the lines in the script according to your suggestion. But it still doesn't work. In the log I find this remark:

expecting string in access or evaluation of 'exAdj' , exAdj/*###*/

at line 212 in Script.jsl

Line 212 is exactly the line you mentioned in your suggestion. Because of this remark, I have changed the script to the form I showed above first.

Other ideas are welcome!

Regards

Markus

Re: Join Bedingungen in JSL übertragen

ih — Tue, 16 Mar 2021 12:48:11 GMT

Ah, turns out you do need the name as a string. Maybe try this instead:

From(
		Table( exAdj, Alias( "t1" ) ),
		Table(
			exAlign,
			Alias( "t2" ),
			Join(
				Type( Inner ),
				EQ( Column( "SerialNumber", "t1" ), Column( "SerialNumber", "t2" ) ) & EQ(
					Column( "ProductionNumber", "t1" ),
					Column( "ProductionNumber", "t2" )
				)
			)
		)
	),

Here is what is a working example that I think does what you are looking for using sample data, maybe if the answer isn't above this would help get you started?

Names default to here(1);

dt1 = Open("$Sample_data/big class.jmp");
dt2 = Open("$Sample_data/big class families.jmp");

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		tb1, tb2
	),
	QueryName( "SQLQuery1" ),
	Select(
		Column( "name", "t1" ),
		Column(
			"age",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		Column( "sex", "t1" ),
		Column( "height", "t1", Numeric Format( "Fixed Dec", "0", "NO", "" ) ),
		Column( "weight", "t1", Numeric Format( "Fixed Dec", "0", "NO", "" ) ),
		Column( "picture", "t2", Analysis Type( "None" ) ),
		Column( "name", "t2", Alias( "name 2" ) ),
		Column(
			"age",
			"t2",
			Alias( "age 2" ),
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		Column( "sex", "t2", Alias( "sex 2" ) ),
		Column(
			"height",
			"t2",
			Alias( "height 2" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		Column(
			"weight",
			"t2",
			Alias( "weight 2" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		Column( "sibling ages", "t2", Analysis Type( "Multiple Response" ) ),
		Column( "sports", "t2", Analysis Type( "Multiple Response" ) ),
		Column( "countries visited", "t2", Analysis Type( "Multiple Response" ) ),
		Column( "family cars", "t2", Analysis Type( "Multiple Response" ) ),
		Column( "reported illnesses", "t2", Analysis Type( "Unstructured Text" ) ),
		Column( "age vector", "t2", Analysis Type( "Vector" ) )
	),
	From(
		Table( dt1 << get name, Alias( "t1" ) ),
		Table(
			dt2 << get name,
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "name", "t1" ), Column( "name", "t2" ) ) &
				EQ( Column( "age", "t1" ), Column( "age", "t2" ) ) &
				EQ( Column( "sex", "t1" ), Column( "sex", "t2" ) ) &
				EQ( Column( "height", "t1" ), Column( "height", "t2" ) ) &
				EQ( Column( "weight", "t1" ), Column( "weight", "t2" ) )
			)
		)
	)
) << Run;

Re: Join Bedingungen in JSL übertragen

markus — Tue, 16 Mar 2021 13:03:08 GMT

Thanks IH
if I introduce your "<< get name" proposal to the script, I get this error message:

at line 211

Any idea more?

Markus

Re: Join Bedingungen in JSL übertragen

ih — Tue, 16 Mar 2021 14:07:52 GMT

Do those columns still exist in your source data tables? for example, does the table opened with the reference dtAdj contain the column 'Month[DateOfTest]'?