365169_fr-FR

liliconcom

Conditions d'adhésion transférées dans JSL

J'ai créé un script avec JMP Query Builder pour fusionner 2 tables. Cela fonctionne bien aussi. Cependant, si je modifie le script, il perd sa référence d'origine à ce qu'était le tableau 1 et ce qu'était 2.

Le problème est la ligne 209 du script. Il reste toujours bloqué à ce stade et ne peut pas trouver les deux tables.

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>Où est l'erreur?</P>
<P>&nbsp;</P>
<P>Merci d'avance pour votre aide!</P>
<P>Markus</P></LINGO-BODY><LINGO-LABS id="lingo-labs-365169" slang="de-DE" mode="NONE"><LINGO-LABEL>Scripting</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-367457" slang="en-US" mode="NONE">Re: Rejoignez Bedingungen dans JSL übertragen</LINGO-SUB><LINGO-BODY id="lingo-body-367457" slang="en-US" mode="NONE"><P>salut<A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/3885" target="_blank"> @markus</A> ,</P>
<P>&nbsp;</P>
<P>Vous stockez des références aux deux tables comme exAdj et exAlign, je pense que vous souhaitez réutiliser ces références dans les lignes qui génèrent des erreurs.Cette partie du code pourrait ressembler à ceci:</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: Transférer les conditions de jointure dans JSL

Merci IH, pour votre réponse!

J'ai changé les lignes du script selon votre suggestion. Mais ça ne marche toujours pas. Dans le journal, je trouve cette remarque:

 

attend la chaîne dans l'accès ou l'évaluation de 'exAdj', exAdj / * ### * /

à la ligne 212 dans Script.jsl

 

La ligne 212 est exactement la ligne que vous avez mentionnée dans votre suggestion. En raison de cette remarque, j'ai changé le script pour le formulaire que j'ai montré ci-dessus en premier.

D'autres idées sont les bienvenues!

Salutations

Markus

Re: Rejoignez Bedingungen dans JSL übertragen

Ah, il s'avère que vous avez besoin du nom sous forme de chaîne.Essayez peut-être ceci à la place:

 

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

Voici ce qui est un exemple de travail que je pense est ce que vous recherchez en utilisant des exemples de données, peut-être que si la réponse n'est pas ci-dessus, cela vous aiderait à démarrer?

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;