cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
guy_yosef
Level III

how to extract data with criteria of JMP table values

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')

;

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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;

View solution in original post

3 REPLIES 3
ian_jmp
Staff

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

guy_yosef
Level III

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";

pmroz
Super User

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;