topic Re: Creating 2nd list box based on 1st list box selections in QlikView

Creating 2nd list box based on 1st list box selections

VivenReddy — Fri, 22 Jan 2021 20:26:58 GMT

I have 2 list boxes. I need the 2nd list box to only list values that have been selected in the 1st list box. When no values are selected in 1st list box then both list boxes should display all values. In the example below, since Honda, Nissan and Toyota have been selected in 1st list box, they should be the only values available in 2nd list box - all 3 values should be white (unselected). The user should be able to make a selection in the 2nd list box which would not affect the values in 1st list box.

Re: Creating 2nd list box based on 1st list box selections

edwin — Fri, 22 Jan 2021 22:50:26 GMT

it sounds like you want the user to select a set and subset of the selection. You will need to have 3 sets of the same data, the first is for users to select, then you need the other two sets to be independent of the first set so that when the second selection is made it does not affect the first. the other two sets need to be associated. you can use alternate states as well but this is simpler

i added a seq so that you want the second selection to show at top

Re: Creating 2nd list box based on 1st list box selections

edwin — Fri, 22 Jan 2021 22:52:12 GMT

i have a trigger that selects the 2nd field and since the 3rd field is associated it will be selectable (white) then in the list box i hid excluded entries

Re: Creating 2nd list box based on 1st list box selections

VivenReddy — Fri, 22 Jan 2021 23:15:58 GMT

Thanks for your response Edwin.

Since my use case will have hundreds of thousands to many millions of values in this field, I would be hesitant to duplicate this data set so many times.

Can we explore a solution using triggers?

Re: Creating 2nd list box based on 1st list box selections

edwin — Fri, 22 Jan 2021 23:27:11 GMT

i think you will definitely use triggers as you cant use association. also are you saying you will have a list that is a million long? or are you saying your fact table runs up to millions? i wasnt suggesting to replicate your whole table - just the fields you want the user to select. so to build the 2nd table:

noconcatenate
load distinct CARS as CARS2, CARS as CARS3 resident ALLCARS;

Re: Creating 2nd list box based on 1st list box selections

VivenReddy — Sat, 23 Jan 2021 00:18:02 GMT

Yes the fact table will have millions of rows but the relevant field could have up to 500,000 distinct values.

Previously I mistakenly mentioned that I wanted a solution using triggers. What I meant to say was that I preferred a solution using alternate states.

Re: Creating 2nd list box based on 1st list box selections

yassinemhadhbi — Sat, 23 Jan 2021 11:49:18 GMT

Good morning,

Have you tried using the hide excluded functionality of ListBox Object ?

Re: Creating 2nd list box based on 1st list box selections

VivenReddy — Mon, 25 Jan 2021 19:35:18 GMT

Sorry @yassinemhadhbi that does not meet my requirement. Please read my initial post again.

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:10:37 GMT

here is a solution that uses alternate states. unfortunately you need to at least create a second copy. to address the requirement that the second list shows white instead of green (bec if the values are selected it will be green not white). the second copy allows it to show white

you need to define an alt state then set the 2nd list as the alt state. add trigger that sets the selection in the alt state.

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:11:19 GMT

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:14:14 GMT

this one has the seq corrected

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:15:09 GMT

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:16:22 GMT

the principle is create a duplicate field that is one to one with teh selected field. this allows you to list the POSSIBLE selections in WHITE.

Re: Creating 2nd list box based on 1st list box selections

edwin — Mon, 25 Jan 2021 20:18:20 GMT

to address the requirement where you dont want the original list touched while selecting from teh second list is done through alt state - one caveat though this adds complexity to your expressions.
you can always go back to the first solution without alt states.

Re: Creating 2nd list box based on 1st list box selections

edwin — Tue, 26 Jan 2021 13:40:35 GMT

To be clear, you dont duplicate your data set, you just duplicate the dimension you want the user to select. you should profile your data and determine the unique number of values. if the values are very limited (normally fields used in dropdown lists should be very limited otherwise it will not be effective) you can use the original solution and create a new table with just the 2 duplicated fields.

if the volume is huge then just duplicate it inside your fact

Re: Creating 2nd list box based on 1st list box selections

VivenReddy — Wed, 03 Feb 2021 00:26:49 GMT

Thanks Edwin. Sorry for the late reply - I got pulled into another project.

See 2 screenshots attached.

In the 1st screenshot CARS3 filter should be only displaying 4 values (Volkswagen, Toyota, Nissan, Honda) in white. All grey values should not be listed.

In the 2nd screenshot a selection has been made in the CARS3 filter. So only Toyota should be highlighted in green and Volkswagen, Nissan, Honda should be the only grey values.

Is this possible? This seems to make more sense and will replicate Qlikview functionality.

Re: Creating 2nd list box based on 1st list box selections

edwin — Wed, 03 Feb 2021 14:17:32 GMT

not sure how you got the screenshots if you were using the QVW i attached:
no selection:

select a couple:

but your last requirement where you want toyota shown as selected and others in gray is a stretch. as it conflicts with your requirement that those not selectable are not shown