Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

use of possible function in set analysis expression

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.

1 Solution

Accepted Solutions
whiteline
Master II
Master II

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.

View solution in original post

8 Replies
whiteline
Master II
Master II

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.

Anonymous
Not applicable
Author

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

whiteline
Master II
Master II

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)

Anonymous
Not applicable
Author

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

whiteline
Master II
Master II

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)

Anonymous
Not applicable
Author

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.

whiteline
Master II
Master II

What do you use as a chart dimension ?

Anonymous
Not applicable
Author

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