913794_en-US

liliconcom

Best way to show data as a dropdown? Not Data filter.

Hi all,

I’m working with two data tables that I’ve joined using primary and foreign keys and they have thousands of data. My goal is to display values from these tables in dropdowns ( currently I am using ComboBoxes), and based on the selections, generate subset or transposed tables dynamically.

Currently, I’m using ComboBox to populate unique values from the data tables, but performance is quite slow and especially with larger datasets. I’ve found that JMP’s built-in Data Filter is powerful but not very customizable for UI integration.

Is there a way to leverage the Data Filter engine behind the scenes and connect it to custom UI dropdowns? Or is there a more efficient approach to achieve responsive, dynamic filtering as a dropdown in JSL?

Any suggestions or examples would be greatly appreciated!

Thanks in advance.

Automation and ScriptingBasic Data Analysis and ModelingRe: Best way to show data as a dropdown? Not Data filter.

Please have a look at the new Filter Views in JMP19.
Maybe you can use the data table itself - and the filter view on the right side of the table:

when you are happy with the view, just open it as new table.

 

further details can be found in this talk : Developer Tutorial: Data Table Enhancements  by @Mandy_Chambers .

Re: Best way to show data as a dropdown? Not Data filter.

We are moving from the older version of JMP to JMP 19. So this solution won't work in the older version.  Any idea about displaying a lot of data faster in the dropdown? The data filter are faster but their UI is not very customizable.

Re: Best way to show data as a dropdown? Not Data filter.

Which part is slow for you? Building the combo box? Searching the values? Creating the subset? 

Can you share example script as there could be some optimizations you could do. 

Re: Best way to show data as a dropdown? Not Data filter.

WHAT I'M DOING:
1. Loading two large data tables (one with 100k+ rows)
2. Joining them by matching keys
3. Extracting unique values from all filter columns and storing them in a separate cache table
4. Creating a custom UI with multiple ComboBox dropdowns (13 filters total)
5. Loading ComboBox values from the cache table
6. User selects values from dropdowns and clicks button to create a filtered subset

// This is simplified version:

Clear Log();

// Step 1-2: Load and join two tables
LoadAndJoinData = Function({},
    dt_results = Data Table("Table1");
    dt_tracker = Data Table("Table2");
    
    // Join tables by matching key
    dt_joined = dt_results << Join(
        With(dt_tracker),
        By Matching Columns(:key1 = :key2),
        Drop multiples(0, 0),
        Output Table("Joined_Data")
    );
    
    Return(dt_joined);
);

// Step 3: Create cache table with unique values - THIS IS SLOW
CreateFilterCache = Function({dt_source},
    Print("Creating filter cache table...");
    
    // Create cache table to store unique values
    dt_cache = New Table("Filter_Cache",
        New Column("Filter_Name", Character),
        New Column("Filter_Value", Character)
    );
    
    column_list = {"Column1", "Column2", "Column3", "Column4", "Column5"};
    
    current_row = 0;
    For(i = 1, i <= N Items(column_list), i++,
        col_name = column_list[i];
        Print("  Processing: " || col_name);
        
        // Extract unique values
        col = Column(dt_source, col_name);
        col_values = col << Get Values;
        
        aa = Associative Array();
        For(j = 1, j <= N Items(col_values), j++,
            val = col_values[j];
            If(!Is Missing(val) & val != "",
                aa[val] = 1;
            );
        );
        
        unique_list = aa << Get Keys;
        unique_list = Sort Ascending(unique_list);
        
        // Store in cache table
        For(k = 1, k <= N Items(unique_list), k++,
            current_row++;
            dt_cache << Add Rows(1);
            dt_cache:Filter_Name[current_row] = col_name;
            dt_cache:Filter_Value[current_row] = unique_list[k];
        );
    );
    
    Print("Cache table created with " || Char(N Rows(dt_cache)) || " entries");
    Return(dt_cache);
);

// Step 5: Load unique values from cache table
GetValuesFromCache = Function({dt_cache, column_name},
    value_list = {};
    
    For(i = 1, i <= N Rows(dt_cache), i++,
        If(dt_cache:Filter_Name[i] == column_name,
            Insert Into(value_list, dt_cache:Filter_Value[i]);
        );
    );
    
    Insert Into(value_list, "", 1);
    Return(value_list);
);

// Step 4-5: Create filter window and load from cache
CreateFilterWindow = Function({dt_final, dt_cache},
    Print("Loading dropdowns from cache...");
    
    // Load from cache - STILL SLOW for large lists
    unique_col1 = GetValuesFromCache(dt_cache, "Column1");
    unique_col2 = GetValuesFromCache(dt_cache, "Column2");
    unique_col3 = GetValuesFromCache(dt_cache, "Column3");
    unique_col4 = GetValuesFromCache(dt_cache, "Column4");
    unique_col5 = GetValuesFromCache(dt_cache, "Column5");
    
    Print("Creating window...");
    
    win = New Window("Custom Filter",
        V List Box(
            Panel Box("Select Filters",
                V List Box(
                    H List Box(
                        Text Box("Filter 1:"),
                        cb_1 = Combo Box(unique_col1, "", <<SET width=""> 0,
                    dt_subset = dt << Subset(
                        Output Table Name("Filtered_" || Format(Today(), "yyyy-mm-dd")),
                        Rows(rowList)
                    );
                    Print("Subset created: " || Char(N Rows(dt_subset)) || " rows");
                );
            )
        )
    );
);

// Main execution
dt_joined = LoadAndJoinData();
dt_cache = CreateFilterCache(dt_joined);  // Create cache once
CreateFilterWindow(dt_joined, dt_cache);  // Use cached values</SET>

Re: Best way to show data as a dropdown? Not Data filter.

Using Summary table is good way of getting unique values

Names Default To Here(1); 

col_of_interest = "age";

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

dt_summary = dt << Summary(
	Group(:age),
	Freq("None"),
	Weight("None"),
	output table name("uniques"),
	private
);
unique_ages = dt_summary[0, 1];
Close(dt_summary, no save);

Re: Best way to show data as a dropdown? Not Data filter.

Thank you for your suggestion. It definitely improved the performance a bit but still it the dropdowns are slow as there is still a lot of unique data for each dropdown. I may have to find a way to use data filter and then add buttons there since that might be best option if I do not find any good solution for the custom dropdowns. 

Re: Best way to show data as a dropdown? Not Data filter.

How about Tabulate with a Data Filter?

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class Families.jmp" );
Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :sex ) ), Row Table( Grouping Columns( :name ) ) ),
	Local Data Filter( Mode( Show( 0 ) ), Add Filter( columns( :sex ), Where( :sex == "F" ) ) )
);

 

Re: Best way to show data as a dropdown? Not Data filter.

You can somewhat manipulate data filters, but you will face limitations quite quickly. Unless the speed issue is within the combo boxes (displaying them) there is still plenty which can be optimized. If you can provide a script to create example table (or example table) which can be used to test the speed, it will be much easier to demonstrate these possible optimizations. 

Re: Best way to show data as a dropdown? Not Data filter.

Perhaps this script would help since this table also has thousands of rows.

You can remove this part and will able to see the issue with the comboboxes.

// Truncate for testing purposes (Truncated it 50 rows this needs to be removed)
unique_flights = unique_flights[1 :: 50];
Show( N Items( unique_flights ) );

// ========================================
// TEST SCRIPT - Single Column Filter (Table Air Traffic)
// Column - Flight ID
// ========================================

Clear Log();


// Todo: The associative array is not an appropriate method for large datasets with many unique values.
// Perhaps use the summary table or something else. Or find unique column values using hashing and storing in a temp table.

// Open the Air Traffic sample data table
dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );

// Safety check
If( Is Empty( dt ),
    Throw( "No data table is open. Please open a data table first." );
);

Print( "Current Data Table: " || (dt << Get Name) );
Print( "Rows: " || Char( N Rows( dt ) ) );
Print( "Columns: " || Char( N Cols( dt ) ) || "\n" );

// METHOD 1: Use Summary table (most efficient for large datasets)
dt_summary = dt << Summary(
    Group( :Flight ID ),
    Freq( "None" ),
    Weight( "None" ),
    output table name( "Temp Summary" ),
    private
);
unique_flights = dt_summary:Flight ID << Get Values;
Close( dt_summary, No Save );

// Truncate for testing purposes (Truncated it 50 rows this needs to be removed)
unique_flights = unique_flights[1 :: 50];
Show( N Items( unique_flights ) );


// // Sort unique values for faster lookup
// unique_flights = Sort List( unique_flights );

// Store reference to data table
::testDataTable = dt;

// Create filter window
::testFilterWindow = New Window( "Test Filter",
    V List Box(
        Text Box( "Select Flight ID:" ),
        cb_flight = Combo Box( unique_flights, "" ),

        H List Box(
            Button Box( "Create Filtered Table",
                sel_flight = cb_flight << Get Selected;

                Print( "\n" || Repeat( "=", 60 ) );
                Print( "Button clicked!" );
                Print( "Selected Flight ID: " || Char( sel_flight ) );

                // Use Where to filter (faster than For Each Row)
                If( sel_flight != "",
                    rowList = ::testDataTable << Get Rows Where( :Flight ID == sel_flight );
                    Print( "Total rows matching filter: " || Char( N Items( rowList ) ) );

                    If( N Items( rowList ) > 0,
                        dt_subset = ::testDataTable << Subset(
                            Output Table Name( "Test Filtered - " || Format( Today(), "yyyy-mm-dd h:m:s" ) ),
                            Rows( rowList )
                        );

                        Print( "\nSUCCESS: Filtered table created!" );
                        Print( "Table: " || (dt_subset << Get Name) );
                        Print( "Rows: " || Char( N Rows( dt_subset ) ) );
                        Print( Repeat( "=", 60 ) || "\n" );

                        dt_subset << Bring Window To Front;
                        Close( ::testFilterWindow );
                    ,
                        Print( "ERROR: No rows match the selected filter" );
                        Print( Repeat( "=", 60 ) || "\n" );
                    );
                );
            ),

            Button Box( "Cancel",
                Close( ::testFilterWindow );
            )
        )
    )
);

I was having the similar issue and I had to switch to data filter unfortunately.

Re: Best way to show data as a dropdown? Not Data filter.

I would think it will be a quite bad experience for the user to scroll through 20k entries in combo box even if it did work properly. You could instead, for example use List Box with additional search, which could be better user experience. You can create one with JSL using Text Edit Box + List Box or use data filter or filter col selector for example. 

Re: Best way to show data as a dropdown? Not Data filter.

Thank you for sharing this script. Indeed this script accurately depicts the issue that I am facing.