Multiple Column Listbox

[#1]

I am looking for a way to show three columns in a listbox. These columns are the account number, account name, and account abbreviation. They are all one-to-one relationships so an account ID of 1234 will always have an account name of OCONUS Labor and an account abbreviation of L1 for example. I have this setup in a table, but the end user wants to have the selection behavior of a list box, i.e. green, white, grey for selected, possible, and excluded.

I am willing to customize a control if needed because I see other areas where this could be useful but thought I'd ask around after searches came close to a solution or missed completely.

[stevedark]

Is this not just a table box with three columns?  If you go to the Properties and then the Presentation tab of your Table Box you can place a Dropdown Select on each column - giving you all the advantages of List Boxes whilst viewing the fields in separate columns.

Another route is to concatenate all three fields, with a separator in the Load Script, eg:

Field1 & ' - ' & Field2 & ' - ' & Field3 as [Concatenated Field],

But it isn't typically a great idea to do this, as it will swell the size of your data.

- Steve

[#3]

The dropdown is close. I want to continue to see all of the rows in the table box. When I select several values from one of the columns in the table box, all unselected rows are hidden. I see that the drop down lists function as expected, but I don't know if my users will even see the dropdown icon, let alone use it.

I have the concatenate listbox and have used the option of an expression in the list box. Both are close, but you loose the ability to select on any column's value. Selections are made based on the Field ignoring the expression values like I would want.

Since the values on a given row in my table are essentially just aliases for the others, it might work. I just don't know yet if there are any functions that require the selection of say the Account ID versus the Account Name.

[swuehl]

Have you tried adding two expression to your list box, using

=only({1} [account name])

and

=only({1} [account abbreviation])

[The only({1}... ) is to show the values also when excluded by a selection]?

[#5]

I don't think I understand this. The list box with expressions is close in that it allows me to make an iterative selection, i.e., pick two then change my mind and control pick a third (or more). It color codes green, grey, white as expected. The problem is that any selection is really a selection of the value in the Field (the left most column). In the table box or simple table chart, I can select values from any column to apply to the current selection.

That is interesting the only({1}[field name]) syntax. I learned something there.

[swuehl]

Sure, you will only select values from the field, not expression values (this is similar to expressions in a straight table, I don't think you select an expression value, even when you are using just a field as expression).

Since the two other fields are only aliases with a 1:1 relation to your account number field, I don't see any additional benefit from selecting also in the other two field, why do you need this?

[stevedark]

This functionality will require a bit more coding, but you should be able to get something at least close to what you are after.

If you do not have one, I suggest adding a counter to your load script for each row, this makes calculations quicker, and is generally a good idea anyway:

LOAD
     1 as RowCount,

     [...]

You can implement the solution below without a RowCount field, but I find this way cleaner.

Create a Chart of type Straight Table.  Add your three fields as Dimensions, and enter a single expression with the syntax:

sum({1}RowCount)

This will return a value regardless of selections.  On the presentation tab you can select to hide the Expression column, so that only the dimensions are displayed.  Again, I would add a Dropdown Select to each - but I take your point about this not being obvious.

Give this a test, it should list all values regardless of selections.  The problem is that rows that are outside the selection will still show in white.  To get the colours you will need to provide Background Colour expressions on each Dimension.

The code for the colour needs to be placed on each dimension, and needs to be something like this, replacing in your own field names:

=if(index('|' & Field1 & '|', '|' & GetFieldSelections(Field1, '|', 999999) & '|') > 0

          and not isnull(GetFieldSelections(Field1)), rgb(0, 240, 16),

          if(sum(RowCount) > 0, rgb(255,255,255), rgb(219, 219, 219)))

The end result should be what you are after.

I've an attached an example of this working on some dummy data.

Hope that helps.

Steve

http://www.quickintelligence.co.uk/

[stevedark · Accepted solution]

The working example now up on the Shared QlikViews site, and is Personal Edition enabled:

QlikView App: Multi Column List Box

- Steve

[#9]

Thanks Steve. This is a good option. I showed it to the end user and they had a couple of requests that I can handle thanks to your start.

It would be awesome if this was added as an out of the box control.

[stevedark]

Hi Rick - glad it works for you, thanks for letting me know.