9434_en-US

liliconcom

how to extract data with criteria of JMP table values<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hi

i am trying to extract data for specifc lot list

for now i extract all data and filter the data i need

but

how do i update my data extraction script to take values directly  from JMP table?

example of data table:

New Table( "lot list",

Add Rows( 5 ),

New Column( "lot",

  Character,

  Nominal,

  Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )

)

);

example of extract criteria for the lots in the table

 

"SELECT * FROM \!"LOTTABLE\!"

WHERE \!"LOT\!" in ('AB457','AB589','XY671','XY001','XY187')

;

Re: how to extract data with criteria of JMP table values

You can use CONCAT ITEMS to create an IN list from a JMP list.  For example:

dt = New Table( "lot list",

     Add Rows( 5 ),

     New Column( "lot",

           Character,

           Nominal,

           Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )

     )

);

lot_list = Column( dt, "lot" ) << Get values;

in_list = "('" || Concat Items( lot_list, "', '" ) || "')";

sql_string = "SELECT * FROM mytable m WHERE m.lot_number IN " || in_list;

Re: how to extract data with criteria of JMP table values

Thanks for the fast answer

i manged to create array, now i need to find a way to concat it

dt = New Table( "lot list",
Add Rows( 5 ),
New Column( "lot",
  Character,
  Nominal,
  Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )
)
);

lots = lot << Get values;
//for example
Show (lots[2]);
/*:

lots[2] = "AB589";

Re: how to extract data with criteria of JMP table values

If I understand correctly, you want to submit a SQL query from JMP using values held in a table. The code below uses a brute force approach to build the query string, and may give you some ideas. In this example, the values came from a user interface that required the user to select one and only one name, and one or more products.

  reps = repsObj << GetSelected;

  prod = prodObj << GetSelected;

  if (NItems(reps)!=1, Beep(); Dialog("WARNING: You need to select a name"); Throw());

  if (NItems(prod)<1, Beep(); Dialog("WARNING: You need to select at least one product"); Throw());

  // Build the resulting query

  SQLQuery = "SELECT * FROM Final WHERE salesrep_name = '"||reps[1];

  if (NItems(prod)==1,

  // Simple to build the query

  SQLQuery = SQLQuery||"' AND product_group = '"||prod[1]||"';",

  // else takes a bit more work

  prodPart = "' AND (product_group = '"||prod[1];

  for(i=2, i<=NItems(prod), i++, prodPart = prodPart||"' OR product_group = '"||prod[i]);

  prodPart = prodPart||"');";

  SQLQuery = SQLQuery||prodPart;

  );

  // Show(SQLQuery); // Debug