How can I add all column values into Custom Filter in JMP Query Builder

[#1]

Hello Sir or Madam,

What I'm trying to do is extract one data table from the 1st database using SQL Query, then I want to put values from one column in the table into Custom Filters in the 2nd JMP Query Builder.

I tried to get values in Col Serial in WO data table extracted from the 1st database like below,

Col_Values = Data Table( "WO" ):Serial << Get Values;

Then, try to put the value into the Custom Filter for Col part_id in JMP Query Builder in the 2nd database.

Where( Custom( "part_id in (Col_Values)", UI( Custom( Base( "Continuous" ) ) ) ) )

But, it didn't work. 

I would be really appreciated if there is any way that I can do this.

Thanks in advance.

[#2 · Accepted solution]

This will get you all of the values from the data table

col_values=data table("WO")[0,0]

[#3 · Accepted solution]

Not sure at which point you are having issues, but first get list of unique values from your column.

Use associative array (doesn't work with floats) and can be slow if you have lots of data (also re-orders your data)

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

uniq_age = Associative Array(Column(dt, "age")) << get keys;

Use summarize, will change numbers to strings which is good thing in this case

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

Summarize(dt, uniq_age = By(:age)); 

Or use Summary platform and data table subscripting / get values.

Then turn that list into a string using concat items and build your query string. You might have to evaluate the value but I'm not sure if this is necessary

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

Summarize(dt, uniq_age = By(:age)); 
uniq_age_in = Concat Items(uniq_age, ", "); 
//uniq_age_in = "'" || Concat Items(uniq_age, "', '") || "'"; // note that if you have string, you need to add quotes

custom_sql_template = "part_id in (¤uniq_age_in¤)";
custom_sql = Eval Insert(custom_sql_template, "¤");


// your sql thing
Where(Custom(custom_sql, UI(Custom(Base("Continuous")))));


// or if evaluation is necessary
Eval(EvalExpr(
	Where(Custom(Expr(custom_sql), UI(Custom(Base("Continuous")))));	
));

[#4]

Hi jthi,

Thank you a lot for your answer. I need to slightly modify the script like below, but overall it works perfectly as I want. I really appreciate your help.

Thanks,

Data Table( "WorkOrder_Serial" ) << New Column( "WO_SN",

Character,

"Nominal",

Formula( "'" || :serial || "'")

);

//I made a new column with quotes as it works well.

Summarize( Data Table( "WorkOrder_Serial" ), SN = By( :WO_SN ) );

uniq_age_in = Concat Items( SN, "," );