How to populate combo box with distinct values?

[#1]

Hi

What's the best way to populate a combo box with the distinct values of a column?? I want to be able to prompt the user as to which data (in this case part number) he/she wants to analyze, and then based on his selection the code would create a new subset table of just the selected value.

I envision the code being something like this:

values = :columnnamehere << Get Distinct Values;

New Window( "Example",

          cb = Combo Box( values ),

  Button Box("OK")

);

Some code here to create a subset table...

Does anybody know what the best practice to do this would be??

Thanks

[#2 · Accepted solution]

A recent JMP blog entry presented different ways to get a list of unique values. One approach that I learned from there uses associative arrays.

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

partnr = Associative Array( (:sex << get values) ) << get keys;

New Window( "Example", cb = Combo Box( (partnr) ), Button Box( "OK" ) );

[#3]

Thanks. This just what I wanted.

However, this is not working for my table. If I use your code with the sample table "Big Class" it works fine. However, if I substitute the table for the one with my data the combo box is blank. Any ideas why?? Changning the columns causes the width of the combo box to change, but its still empty. I checked the column properties and they are the same as the ones in Bog Class.

Any ideas?

Thanks again

[#4]

I found out this happens when there is a blank cell in the column. If the blank cells are deleted, it works fine. Any idea why it won't work with the blank cells??

[#5]

I got it to work using a For loop that ignores all blank cells. Here's the code for anybody that's curious:

partArray = Associative Array();

For( i = 1, i <= N Rows( dt ), i++,

          If( Part[i] != "",

                    partArray << Insert( Part[i] )

          )

);

partArray = partArray << get keys;

[#6]

It appears that combo boxes get messed up with blank entries.  If you use a listbox instead it works, although one of the entries is a blank, which you don't want.

Here's the function I use to get unique values from a column, which uses the Summarize command:

//---------------------------------------------------------------------------

/* Function Name  Get Unique Values

Description:      Get the unique values for a column.  For example:

      Indications

      ------------

      AAAAA

      BBBBB

      AAAAA

      CCCCC

      CCCCC

unique_list = get unique values (:Indications)

will return {"AAAAA", "BBBBB", "CCCCC"}

Arguments:

dtcol       column descriptor to calculate unique values for

*/

Get Unique Values = Function( {dtcol},{default local},

// Eval/expr trickery to get this to work.

    eval(eval expr(

        Summarize(unique_list = By(expr(dtcol)))));

    unique_list;

);    // end get unique values

Here's an example that shows how to use the function.  I read in the Car Physical Data table beforehand and blanked out some entries in the Country column.

dt = Open( "$sample_data\Car Physical Data.jmp" );

country_list = get unique values( Column( dt, "Country" ) );

for (i = nitems(country_list), i > 0, i--,

    print(i);

    if (country_list[i] == "",

        remove from(country_list, i);

    )

);

nw1 = New Window( "Example",

    cb = Combo Box( country_list ),

    Button Box( "OK", nw1 << close window )

);

nw2 = New Window( "Example",

    lb = List Box( country_list ),

    Button Box( "OK", nw2 << close window )

);

I suppose the best thing to do is to add the blank removal to the function.

[#7]

Nice function! If the column is numeric, then Summarize() is here actually preferable to the Associative Array() method. Summarize puts double quotes around numeric levels whereas the items in the list generated by AA<<get keys stay numeric. A combo box can only be populated by text entries. Numbers are ignored.

For other uses when the number rather than Char(number) is required, the AA approach may be a better choice. The AA method is also general as it is not limited to Columns and accepts many kinds of data types as list items (matrices, expressions...).

[#8]

I never tried the function on a numeric column.  Interesting results.

I did some performance testing of associative arrays vs summarize for determining unique values, and it was pretty even.  As an example, I used a table with 180,000 rows.  Both methods took 4 seconds to determine unique values for 6 columns.