Use of ONLY Function

[tresesco]

Hi all,

Somewhere i read the use of ONLY function with AGGR function.

"Conveniently, it is also an aggregation expression, allowing you to use set analysis in some cases where you otherwise wouldn't be able to."

Can anybody explain the above staement and give some useful uses of the ONLY function? I know the basics of what it returns, so no need to explain the basics.

Thanks.

[johnw · Accepted solution]

This isn't a very good example, but lets say you have this data:

Data:
LOAD * INLINE [
ID, Type, Value
A, X, 10
B, X, 20
C, Y, 30
];

You build a straight table with ID as the dimension, and Value as the expression.

But now you only want to show values for Type X. You'd like to do this with set analysis, but you can't without an aggregation function. Fortunately, only() is an aggregation function, so you can write this:

only({<Type={'X'}>} Value)

The same thing would apply if you were using an aggr() function. For instance, instead of a table, you could write this:

concat(aggr(Value,ID),',')

That should return 10,20,30. But what if, again, you wanted to restrict it to only Type X?

concat(aggr(only({<Type={'X'}>} Value),ID),',')

That should return 10,20.

I could just be explaining the basics to you, but I'm not sure what more than the basics needs to be explained.

[tresesco]

Thanks John, for your explanation.

I used to use MAX function with Set Analysis (in same situation). Even here, it works fine if I replace ONLY with MAX.

Regards,

tresesco

[johnw]

tresesco wrote:I used to use MAX function with Set Analysis (in same situation). Even here, it works fine if I replace ONLY with MAX.

Honestly, I usually use max() in a situation like this as well. Often I EXPECT only a single value, but if there's more than one value, I'd rather pick one by using max() than return NULL by using only(). So you'd only want to use only() when you want NULL when there are multiple values. I don't typically encounter those situations. Still, I'm fairly certain I've used it in a live application. I wonder where, and why? Hmmm.

[#5]

Hi,

I'm new to QlikView and despite searching couldn't find an answer to this problem :-

How do I return one field based on the value of another ie return the name of the City with the lowest population. From the tutorial I have created the following syntax :-

=

'The Smallest City is '&only({$<[Population(mio)]={$(=MIN({$}[Population(mio)]))}>} [Capital])&' with '&Min ({P}[Population(mio)])&' inhabitants'

This is fine except where I have multiple values for the minimum. How do I code around this? I thought of maybe concat the fields and then minstring but I couldn't seem to get that to work in the above expression

minstring({1}num([Population(mio)],'0,000,000.0000')&[Capital])

Thanks in advance for your time and help

[johnw]

If multiple cities have the same population, wouldn't you want to display them all instead of some "random" city? So I'd think you'd want to use concat() instead of only() in your expression. And here's another way to approach the problem. Not sure which would perform better:

concat(aggr(if(rank(-"Population(mio)",1,1)=1,"Capital"),"Capital"),', ')

Edit: Wait, it's much simpler than that. You should be able to do this:

firstsortedvalue("Capital","Population(mio)")

Oh, no, because if more than one Capital has the highest population, it would return null, and you're back to your original problem. Well, never mind, but I'm posting it anyway in case it stimulates any ideas that are helpful.

[#7]

Thanks John, Your first solution works exactly as I want it too

I'm still getting my head round QlikViews syntax

As for performing better - Do you have any pointers as to what is 'expensive', or performs well, in Qlikview from your experience that you could share (or point me at a source)?

I appreciate your time and help on this.

[johnw]

Well, one general rule of thumb is that set analysis will perform better than an equivalent if(). So off hand, I would expect your expression with concat() instead of only() to perform better than the other one I mentioned. But there are exceptions to that rule.

As far as tips, I like to think about how QlikView is likely to calculate each expression. Set analysis is kind of like using an index to read your data, while if() is like reading the entire table, and then discarding rows you don't like. So to me, understanding some of the basics of QlikView performance is very much like understanding when a database system will use indexed reads vs. table scans. So assuming you have a database background, perhaps that will help visualize what's going on, even if it's not exactly the same thing.

For your particular example, the set itself is looking for the minimum population. While on the surface this might require reading every row, it's also possible that it does not. I believe that QlikView stores separate lists of values for every field. These might be sorted rather than unsorted lists. If so, it could probably just pick the first possible value to get the minimum without actually reading the whole list, let alone the whole table. And if so, then it could do the set analysis part as well without reading the whole table, so it should be fast. But it's also possible that I'm wrong about how it gets the minimum, and it would have to do a "table scan", in which case it would be more difficult to predict which way of scanning the table would be faster, the set analysis approach or the rank() approach.

My money's on set analysis in this case, but I'm not particularly confident in that. Chances are any difference won't be noticeable to users, and would require repeated testing while measuring the milliseconds required to calculate. I only rarely go to that effort, usually only when the chart is so slow that a user might notice.

[IAMDV]

John - Very good post. I had learnt from your explanation. Totally valid...

Many thanks

[Anonymous]

Hi all,

I am trying to accomplish something similar; use expression to filter the value 'On hold' away from a listbox displaying StatusName.

Based on the above examples I tried

=Only({<StatusName-={'On hold'}>}StatusName)

AND

=Only({<StatusName={'*'}-{'On hold'}>}StatusName)

but they did not work. In fact, after the change the listbox displays only its caption - no values at all.

Can you think of something that is wrong in my approach?

Thanks,

Juho