Indirect Record Selection

[#1]

I feel like I'm struggling with some simple concepts. I'm not sure if this was covered in the tutorials I took a year ago but regardless I'm drawing a blank.

Anyway, let's say I have the following table:

Survey / Question / Value

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

1     -     A     -     Z

1     -     B     -     Y

1     -     C     -     X

2     -     A     -     W

2     -     B     -     V

2     -     C     -     U

3     -     A     -     T

3     -     B     -     S

3     -     C     -     R

Now suppose I'd like to select from a list of possible values from question A:

A

------

Z

W

T

But when I select 'W', I don't really want all and only records with 'W' but instead want all records related to surveys with 'W' as a value for question 'A'.

It is in a sense selecting records indirectly. One idea is to create an inline table and use an expression that relates surveys to the value selected but I would want the list to be created dynamically so an inline table doesn't seem like a good idea.

[swuehl · Accepted solution]

Hi MonkeyHouse,

so you have maybe something like a list box expression

=if(Question='A',Value)

which gives you the possible Values and then you select 'W' from that, right?

And you expect e.g table with all Surveys #2 instead of only 2 - A- W?

For example, you could create a straight table with dimension Survey, Question, Value, and then use

=sum({<Survey=p({<Value = p(Value)>}), Value=, Question=>} 1)

as expression. If supress zero values is enabled in presentation tab, you will only see the Survey = 2 lines (three lines). You could also hide the column.

Instead of using the expression just to show / hide rows, you could also use the set expression in any value calculation.

Hope this helps,

Stefan

[#3]

ok, this makes sense to me. I understand I need to create a chart -> straight table with an expression that changes values depending upon the selection of a list box so that values can be dynamically filtered out.

I am having some problems with the example provided. It has problems with the comma after the 'Value=' and seems to always evaluate to '1'.

[swuehl]

Maybe check attached. I think you probably have a typo in your expression (or I do in my posting, but can't find), if the syntax of the set expression is not correct, the expression will indeed evaluate to 1.

Regards,

Stefan

[#5]

I think I understand why it isn't working for me. When presenting the problem I simplified it by describing a single table. In reality It's two related tables. i get your solution to work when presented all in one table ( even though it still doesn't like the syntax on the comma). I'm still trying to work out why having the Survey ID pulled from a seperate table makes a difference to the results.

[swuehl]

Could you modify my above sample so that the data model matches yours and then repost it here?

It should be possible to adapt it then to your needs. I also could have a look into the comma issue then.

On which version of QV are you working, by the way?

Regards,

Stefan

[#7]

I upgraded to the most current version which removed the syntax error.

I figured out that my issue is that my 'Value' list box uses a Field Expression on the general tab to limit the values displayed. I'm guessing that becaue of this the selection name used in your expression doesn't match.

I basically have results from a certain question in the box and this is how I filtered it.

So what name for the field can be used?  Is it something like this: <List Box Name>. <Expression> ?

[swuehl]

Well, using an expression in the list box to filter Value is what I also did in my sample aove, but it is basically just selecting in field Value (which you can see if you also create a list box for field Value).

So there has to be something else, right?

Maybe recheck my application with your data model and implementation or try building a small sample app and post it here to the forum.

Regards,

Stefan

[#9]

Well I'm still using the trial version so I couldn't use your project but I worked on it more and figured it out.

My filtering expression was using a mnemonic to filter out question and not the actual question so if my expression to filter is: '=If(Mnemonic='BB',Value)

Then the chart expression should be changed to:

'=sum({<Survey=p({<Value=p(Value)>}), Value=, Mnemonic=>} 1)

I'm not sure I fully understand the Sum statement. Can you summarize it?

Also can aggragate expressions be added to the chart as a column?

[swuehl]

The important thing is the set expression:

{<Survey=p({<Value=p(Value)>}), Value=, Mnemonic=>}

If you are not familiar with set expressions, please look into the HELP or I also like to suggest this blog:

http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

The set expression can't be used stand alone, you need an aggregation function, I picked sum() for instance.

What a set expression does is that can change your field value selection, but only in the scope of the object's expression (so no worries, you won't add up with changed global selections).

My expression tells QV to select the Survey which are linked to the current possible Value field values, in our example above, we manually select Value value 'W' and this tells QV to select Survey #2. Then we tell QV to clear the explicit selections in fields Value and Question, so we end up (in our simplified example), with no selections in Value and Question and Survey #2 selected. This is exactely what we would like to see (and should be equivalent to removing the expression from the table (better replacing it with 1) and manually selecting #2 while Question and Value keep cleared.

So if you want to add an aggregation in a column to the chart, this should not be a problem, in fact, it is as easy as writing instead of

=sum({<Survey=p({<Value=p(Value)>}), Value=, Mnemonic=>} 1)

something like

=sum({<Survey=p({<Value=p(Value)>}), Value=, Mnemonic=>} Sales)

or similar. Just use the set expression withing your aggregation function as needed.

Hope this helps,

Stefan