365169_ko-KR

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" mode="NONE"><LINGO-LABEL>스크립팅</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-367457" slang="en-US" mode="NONE">Re : JSL übertragen에서 Bedingungen 가입</LINGO-SUB><LINGO-BODY id="lingo-body-367457" slang="en-US" mode="NONE"><P>안녕<A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/3885" target="_blank"> 마쿠스</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에서 조인 조건 전송

귀하의 답변에 감사드립니다!

귀하의 제안에 따라 스크립트의 줄을 변경했습니다. 하지만 여전히 작동하지 않습니다. 로그에서 다음과 같은 내용을 발견했습니다.

 

'exAdj', exAdj / * ### * / 액세스 또는 평가에 문자열이 필요합니다.

Script.jsl의 212 행

 

212 행은 제안에서 언급 한 것과 정확히 일치합니다. 이 발언으로 스크립트를 위에서 보여 드린 형태로 변경했습니다.

다른 아이디어도 환영합니다!

문안 인사

Markus

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;