365169_zh-TW

liliconcom

在JSL中轉移的加入條件

我使用JMP Query Builder創建了一個腳本來合併2個表。 那也很好。 但是，如果我修改腳本，它將失去對錶1和表2的原始引用。

問題是腳本中的第209行。 此時，它總是卡住，無法找到兩個表。

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

</CODE></PRE>
<P>錯誤在哪裡？</P>
<P>&nbsp;</P>
<P>預先感謝您的幫助！</P>
<P>馬庫斯</P></LINGO-BODY><LINGO-LABS id="lingo-labs-365169" slang="de-DE"><LINGO-LABEL>腳本編寫</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-367457" slang="en-US">回复：加入貝丁格根參加JSLübertragen</LINGO-SUB><LINGO-BODY id="lingo-body-367457" slang="en-US"><P>你好<A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/3885" target="_blank">@markus</A> ，</P>
<P>&nbsp;</P>
<P>您將對這兩個表的引用存儲為exAdj和exAlign，我想您想在拋出錯誤的行中重複使用這些引用。該部分代碼可能看起來像這樣：</P>
<P>&nbsp;</P>
<PRE><CODE class=" language-jsl">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" )
    )
   )
  )
 ),

回复：轉移JSL中的加入條件

感謝IH，謝謝您的回答！

我根據您的建議更改了腳本中的行。 但這仍然行不通。 在日誌中，我發現了這一點：

 

預期在訪問或評估“ exAdj”，exAdj中的字符串/ * ### * /

在Script.jsl的第212行

 

212行正是您在建議中提到的行。 因此，我已將腳本更改為上面顯示的形式。

歡迎其他想法！

問候

馬庫斯

回复：加入貝丁格根參加JSLübertragen

啊，事實證明您確實需要名稱作為字符串。也許試試看：

 

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" )
    )
   )
  )
 ),

這是我工作的例子思考您使用樣本數據正在尋找什麼，也許如果答案沒有超出此範圍，它將幫助您入門？

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;