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

how to extract data with criteria of JMP table values

guy_yosef — Mon, 01 Sep 2014 15:12:24 GMT

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

ian_jmp — Mon, 01 Sep 2014 17:52:57 GMT

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

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

guy_yosef — Tue, 02 Sep 2014 07:11:34 GMT

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

pmroz — Tue, 02 Sep 2014 13:56:51 GMT

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;