365169_es-ES

liliconcom

Condiciones de unión transferidas en JSL

Creé un script con JMP Query Builder para fusionar 2 tablas. Eso también funciona bien. Sin embargo, si modifico el script, pierde su referencia original a lo que era la Tabla 1 y lo que era 2.

El problema es la línea 209 del guión. Siempre se atasca en este punto y no puede encontrar las dos tablas.

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>¿Dónde está el error?</P>
<P>&nbsp;</P>
<P>¡Gracias por su ayuda de antemano!</P>
<P>Markus</P></LINGO-BODY><LINGO-LABS id="lingo-labs-365169" slang="de-DE"><LINGO-LABEL>Scripting</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-367457" slang="en-US">Re: Únete a Bedingungen en JSL übertragen</LINGO-SUB><LINGO-BODY id="lingo-body-367457" slang="en-US"><P>Hola<A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/3885" target="_blank"> @markus</A> ,</P>
<P>&nbsp;</P>
<P>Almacena referencias a ambas tablas como exAdj y exAlign, creo que desea reutilizar esas referencias en las líneas que arrojan errores.Esa parte del código podría verse así:</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: transferir condiciones de unión en JSL

¡Gracias IH, por tu respuesta!

Cambié las líneas en el guión de acuerdo con su sugerencia. Pero todavía no funciona. En el registro encuentro este comentario:

 

esperando cadena en el acceso o evaluación de 'exAdj', exAdj / * ### * /

en la línea 212 en Script.jsl

 

La línea 212 es exactamente la línea que mencionó en su sugerencia. Debido a este comentario, cambié el script al formulario que mostré arriba primero.

¡Otras ideas son bienvenidas!

Saludos

Markus

Re: Únete a Bedingungen en JSL übertragen

Ah, resulta que necesitas el nombre como una cadena.Quizás intente esto en su lugar:

 

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

Este es un ejemplo de trabajo que yo pensar ¿Qué está buscando usando datos de muestra, tal vez si la respuesta no está arriba, esto le ayudaría a comenzar?

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;