topic Re: use of possible function in set analysis expression in QlikView

use of possible function in set analysis expression

Anonymous — Mon, 24 Sep 2012 21:18:36 GMT

Hello,

I am trying to use set analysis as an expression of a pivot table chart. There are 2 tables that are relevant to achieving the logic that is desired.

One table has "projections" and the other "results". In essence I am attempting to line up results with projections. I have stripped down the logic a little so that I can hopefully convey the issue that I am facing.

If I use an expression like this to reveal the projections version number of interest, it works. Let's say this shows that version 1.2 is the answer.

=max( {<projections.os=p(os)>} if(projections.pl_ver_num <= pl_ver_num, projections.pl_ver_num))

The result of this expression needs to feed into another expression to finally show me the entire list of projections that are part of the 1.2 version.

Instead, I would like to achieve this action in a single expression by extending the logic in my set analysis expression BUT it is not working as expected? I am wondering if it is a syntax error?

max( {<projections.os=p(os), projections.pl_ver={"<=$(=p(pl_ver))"}>} projections.pl_ver)

If I replace the test for the version number in the set analysis expression above (i.e., the stuff between the {}) with {"<=1.2"} then this expression works which leads me to believe that my syntax is at fault?

If I create a separate text box for testing, and use the possible funciton on pl_ver I can see that it returns 1.2, which is expected.

Any help would be appreciated.

Re: use of possible function in set analysis expression

whiteline — Tue, 25 Sep 2012 09:42:33 GMT

Hi.

You can use 'search' camabilities of set analysis instead:

=max( {<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver)

It should select from projections.pl_ver only thouse values that have (projections.pl_ver_num<=pl_ver_num)=true.

Re: use of possible function in set analysis expression

Anonymous — Tue, 25 Sep 2012 16:31:26 GMT

Thank you it worked, appreciate your feedback. Can you please help me extend this further such that I can embed the "max" inside the set analysis expression. Again, I struggle with the syntax.

I would like a single version as the outcome. Currently the set returns a set of values 0, 1.1, 1.2. I want 1.2 as a result.

So, something like

projections.pl_ver={"=$(=max(projections.pl_ver_num<=pl_ver_num))"}>} projections.some_field

Thanks in advance

Re: use of possible function in set analysis expression

whiteline — Tue, 25 Sep 2012 17:50:37 GMT

Sorry, didn't understand your issue.

There is max already:

=max( {<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver_num)

Of course you can sort it and return the last value with firstsortedvalue():

=firstsortedvalue({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver, -projections.pl_ver_num)

Re: use of possible function in set analysis expression

Anonymous — Tue, 25 Sep 2012 18:18:00 GMT

Apologies.

Currently, the max() evaluates to a version # (1.2). This is then fed into another expression to index a correspoding value (a battery current measurement in my application). So version 1.1 may return a current value of 5mA, 1.2 a value of 8mA.

The max() expression that you have helped to solve returns ver 1.2, now I need to parse out the corresponding current value of 8mA.

I am essentially trying to retrieve current[1.2] vs. current[1.1] vs current[1.0]. The max expression in the set analysis should return the 1.2, so hopefully this clarifies what I am trying to achieve:

projections.pl_ver={"=$(=max(projections.pl_ver_num<=pl_ver_num))"}>} projections.battery_current

Re: use of possible function in set analysis expression

whiteline — Tue, 25 Sep 2012 18:27:15 GMT

You could try this one:

=firstsortedvalue({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.battery_current, -projections.pl_ver_num)

Or this one:

=Only({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num=rangemin(max(projections.pl_ver_num), pl_ver_num)"}>} projections.battery_current)

Re: use of possible function in set analysis expression

Anonymous — Tue, 25 Sep 2012 18:57:08 GMT

Both are great suggestions. I am attempting to execute this expression in a pivot table chart. Does this matter? None of the ideas seem to be working. Your logic seems sound to me.

Re: use of possible function in set analysis expression

whiteline — Tue, 25 Sep 2012 18:58:21 GMT

What do you use as a chart dimension ?

Re: use of possible function in set analysis expression

Anonymous — Tue, 25 Sep 2012 20:05:49 GMT

I have many dimensions none of which directly feed into the expression.

Through table association, though, there are many indirect values that are used.

For example, I have a software ID as a dimension and it is linked to an OS but OS is not directly referenced nor selected by a user. This is why I used the possible function to select the OS.

The same is true for the pl_ver_num field above. The table dimension software ID also indirectly chooses the various pl_ver_num's (1.1, 1.2). I also have a test ID that is a dimension. In essence I store battery current for a number of IDs across various software versions.

I took the first expression that you sent me (your first post) and created a field in my pivot table (for debugging) and it yields the correct version # across all dimensions. I have created a new expression in the table, with the new suggestions that don't seem to kick in.

I will add that I just tried to place your "firstsortedvalue" suggestion in a text box, becasue it makes too much sense to me, and this is working. I am playing further to see why it is not working in the context of the pivot table.

Regards