365169_ja-JP

liliconcom

JSLで転送された結合条件

JMP Query Builderを使用して、2つのテーブルをマージするスクリプトを作成しました。 それもうまくいきます。 ただし、スクリプトを変更すると、表1と表2への元の参照が失われます。

問題は、スクリプトの209行目です。 この時点では常にスタックし、2つのテーブルを見つけることができません。

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">Re：JSLübertragenでBedingungenに参加する</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" )
    )
   )
  )
 ),

Re：JSLでの結合条件の転送

あなたの答えをありがとうIH！

私はあなたの提案に従ってスクリプトの行を変更しました。 しかし、それでも機能しません。 ログで私はこの発言を見つけます：

 

'exAdj'、exAdj / * ### * /のアクセスまたは評価で文字列が必要です

Script.jslの212行目

 

行212は、まさにあなたが提案で言及した行です。 この発言のために、スクリプトを最初に上に示した形式に変更しました。

他のアイデアも大歓迎です！

よろしく

マーカス

Re：JSLübertragenでBedingungenに参加する

ああ、文字列として名前が必要なことがわかりました。代わりにこれを試してみてください：

 

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;