Filtering a datatable with a custom filtering application

[#1]

Hello All,

I am building an application/dashboard with a bunch of reports inside it. At the end I would like to give users the option to make a "query" of the main datatable and save it if they have a use for it later on.

Currently, I have a button which opens another window and prompts the user to select filtering columns (from a preselected list).
Once they select them, they press another button which should then create a list box for each of the filtering columns with all available distinct values. My thought is from here the user would select the values they want the filtering to occur for in each list box and then press a final button to save those values and then a filter/where clause would be build which will subset the main datatable and save it for them in a known location.

I believe I am halfway there. I get the first window and filter column selection to work. Then I can display the values in list boxes but am unable to create the right button to save those selected values. Also, I believe I am running into the issue of not knowing how many filter columns the users will create therefore I have to have an expression to define an unknown number of columns. I saw some examples with Eval(Parse(Eval Into statements but have not gotten it yet.

Any help will be appreciated. Maybe there is a much more streamlined way to do it.

Here is some of the code I have so far

filter_col_append_expr = Expr(
	lub1 << Append(List Box(column(dt1,Char(col_filter[count_i])) <<GetAsMatrix));
	vlb3 << Append(Spacer Box(size(1,10))););

query_expr = Expr(
	query_window = New Window("Database Query",
		Spacer Box( size (1,5)),
		vlb3 = V List Box(Text Box("Select All Filter Columns"),
		H List Box(Spacer Box( size (1,5)),
		lb3 = List Box({"XXX","YYY","ZZZ","AAA"},
		max selected(8),On Change( col_filter = lb3 << Get Selected;))),
		Spacer Box( size(1,10)),
		Button Box("Select Filter Columns",query_window<<Append(lub1=Lineup Box(N Col(4)));
			For( count_i=1, count_i<=NItems(col_filter),count_i++,filter_col_append_expr;)))));

//The button which will run the whole thing is here. Some code is ommited
Panel Box("Database Query", Button Box("Query",query_expr;query_window << Append(bb3 = Button Box("Select Filter Values",query_values = lub1<<Get Selected));)))

[#2 · Accepted solution]

Have you considered building data filter based on the user selected columns?

[#3]

Yes but the less clutter the better since not many of the users are new to JMP and I want to make it as easy for them to visualize as possible and with the press of a button to get their filtered data.

Doing it with the local filter could work too

[#4]

I would build data filter (which you can cleanup if needed) but here is one option of using List Boxes

Names Default To Here(1); 

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

options = dt << Get Column Names("String");

filter_col_append_expr = Expr(
	Summarize(dt, colvals = By(Eval(selection))); // Note that Summarize this will change numeric values to strings
	lub1 << Append(List Box(colvals));
);

query_expr = Expr(
	query_window = New Window("Database Query",
		Spacer Box(size(1, 5)),
		vlb3 = V List Box(
			Text Box("Select All Filter Columns"),
			H List Box(
				Spacer Box(size(1, 5)),
				lb3 = List Box(
					options,
					max selected(8)
				)
			),
			Spacer Box(size(1, 10)),
			Button Box("Select Filter Columns",
				cols = lb3 << get selected;
				Try(lub1 << Delete Box());
				lub_collector << Append(lub1 = Lineup Box(N Col(4)));
				For Each({selection}, cols,
					filter_col_append_expr
				);
			),
			lub_collector = V List Box();
		)
	)
);

//The button which will run the whole thing is here. Some code is ommited
nw = New Window("",
	Panel Box("Database Query",
		Button Box("Query",
			query_expr;
			query_window << Append(
				bb3 = Button Box("Select Filter Values",
					vals = (lub1 << XPath("//ListBoxBox")) << get selected;
					user_selections = Associative Array(cols, vals);
					show(user_selections);
				)
			);
		)
	)
);
 

[#5]

Thanks that works as I was intending.

However, the next step I am a bit at an impasse. 

To build the data filter I was thinking about directly putting the associate array with the user selections but that is not working.

Then I thought about breaking that goes into the data filter down and using Expr and other string commands to build it to look exactly as if I had used the red triangle after I built an interactive data filter but I think I am going wrong somewhere on the syntax.

This is my code from your BigClass example

where_clause_list = {};
where_clause = "";
keys = user_selections << getkeys;
values = user_selections << getvalues;
For ( i=1, i<=NItems(keys), i++, 
    // Inner loop iterating over items for each category
    For Each( {value}, values[i],
        // Action for each category-item pair
        (where_clause = where_clause || ":"||keys[i] || " ==" || " " || Char(value) || " &"|| " ");
    InsertInto(where_clause_list, where_clause);
    where_clause = "";));
where_clause="";
For Each({where},where_clause_list,where_clause=where_clause||where);
where_clause = Left(where_clause,Length(where_clause) -3);
Show(where_clause_list);
Show(where_clause);
where_expr = Expr(where_clause);
key_clause="";
For Each({key},keys,key_clause=key_clause||"Ascolumn("||key||")"||" ,");
key_clause = Left(key_clause,Length(key_clause) -2);
keys_expr=Expr(key_clause);
Show(key_clause);


dt << Data Filter(Add Filter(columns(key_expr),Where(where_expr),
Mode( Select( 0 ), Show( 1 ), Include( 1 ))));

However, is there a more direct and intuitive way to do this?

[#6]

If you have the selections already in associative array, do you need the data filter anymore? My earlier suggestion of using Data Filter was to avoid creation of the "extra" list boxes to select row values.

[#7]

Once I have the selections (both the column names and values) I wanted to subset the data and extract only what is filtered by the user selections. For that I would need some data filter right?

[#8]

Depending on what you mean by "extract" you can use << Get Rows Where + Subset or << Select Where + Subset or << Select Where + Hide and Exclude

[#9]

Yes exactly I wanted to use some of those methods but was unsure how to do it when I do not know which column and values the user has selected. The selections are in an associative array now but translating them into a Where clause was the difficulty for me. That is why I tried to construct the Where clause with the previous chunk of code I showed but I was not getting it done. Maybe there is a more straightforward way to work with the associative array to construct the where clause

[#10]

Directly using data filter would be the simplest option but we have already ruled that out. Should the user selections be combined by AND or by OR?