topic Re: Creating one list box off of two different fields in QlikView

Creating one list box off of two different fields

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

Is there a way to create a list box off of two different fields?

I have a few tables that I concatenate together that look like this:

Pkey T1_Dummy Control # T2_Dummy

123 1 - -

234 1 - -

- - 123 1

- - 234 1

I want to create a column off of the T1/T2 Dummies, so that a user can make a selection based on what data they would like to see

Like this:

Pkey T1_Dummy Control # T2_Dummy Overall_Dummy

123 1 - - Direct

234 1 - - Direct

- - 123 1 Indirect

- - 234 1 Indirect

This is great and all, but I also want the user to be able to make a selection for BOTH tables within that same list box, without having to actually hold control down and click 'direct' and 'indirect'...

so I think I would need a second column:

Pkey T1_Dummy Control # T2_Dummy Overall_Dummy Overall_Dummy2

123 1 - - Direct Overall

234 1 - - Direct Overall

- - 123 1 Indirect Overall

- - 234 1 Indirect Overall

Is this possible? Or is there a shortcut to doing this that I may not know about?

EDIT: I've added a QVW as an example to show what this would look right now

Re: Creating one list box off of two different fields

Anonymous — Mon, 14 Nov 2016 15:23:45 GMT

Hi Duke,

It's hard to gather the context of your question from only the information here. Can you post an example document? It would be easier to answer your question with a better idea of your data model.

When specifically do you want it the values of 'overall', 'direct', and 'indirect' to be applied?

Re: Creating one list box off of two different fields

lucasdavis500 — Mon, 14 Nov 2016 17:17:49 GMT

I've attached an example.

Re: Creating one list box off of two different fields

lucasdavis500 — Mon, 14 Nov 2016 17:19:36 GMT

You will notice that to switch between the two tables, you can select "Direct" or "Indirect" from one list box. In order to get the value of BOTH tables, the user must DESELECT all current selections in the listbox to get OVERALL, they cannot simply click "overall", the results are not correct. I would like one listbox to show "Overall", "Direct", "Indirect" so the user can make their choice intuitively, without having knowledge of how QV selections works.

Re: Creating one list box off of two different fields

stigchel — Mon, 14 Nov 2016 17:51:29 GMT

It is not quite clear what your real data model is like, but if I understand your need than I think you can use a crosstable for that. See attached

Re: Creating one list box off of two different fields

lucasdavis500 — Mon, 14 Nov 2016 18:47:50 GMT

This seems like it would work,

I don't understand where you found these values:

CrossTable(Origin, Table,1) Load

Origin, Table, 1

where did the above tables come from? I don't see them referenced anywhere

Re: Creating one list box off of two different fields

stigchel — Tue, 15 Nov 2016 10:47:20 GMT

These are new fields created using the crosstable function. See the help for crosstable

Crosstable

A cross table is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. month names) will be stored in one field - the attribute field - and the column data (month numbers) will be stored in a second field: the data field.

The syntax is:

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.