15 Replies Latest reply: Nov 18, 2015 5:56 AM by Tresesco B

# Use of ONLY Function

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.

• ###### Use of ONLY Function

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

Data:
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.

• ###### Use of ONLY Function

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

• ###### Use of ONLY Function

`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.

• ###### Use of ONLY Function

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])

• ###### Use of ONLY Function

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.

• ###### Use of ONLY Function

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.

• ###### Use of ONLY Function

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.

• ###### Use of ONLY Function

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

Many thanks

• ###### Re: Use of ONLY Function

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

• ###### Re: Use of ONLY Function

Juho

If you want help with this issue, I suggest that you start a new thread with your question. This thread is answered and you are unlikely to get help here.

Jonathan

• ###### Re: Use of ONLY Function

Hi John,

This helped me as well.

had to work on it a bit to fit my requirements, but got it right

concat(aggr(if(rank(-aggr(min(Score),CQ_Agent,Parameter),1,1)=1,Parameter),CQ_Agent,Parameter),', ')

Thanks,

ARN

• ###### Re: Use of ONLY Function

Hi John

I find this thread really useful, i managed to get some idea from your post about below:

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),',')

However, i use the type X as date formatted as YYYY-MM-DD. I'm tyring to display the data from last 10 days.

So i have the following data:

ActDate               Suppgroup      Value

2014-06-01          A                    10

2014-06-01          B                    15

2014-06-02          A                    20

2014-06-02          B                    25

2014-06-03          A                    30

2014-06-03          B                    35

2014-06-04          A                    40

2014-06-04          B                    45

So if today's date is 2014-05-04 and i want to display the information for date equal to 2014-06-02

my formula is       =concat(aggr(only({\$<[actdate]={">=\$(=today()-3)"}>}Value),suppgroup),',')

the output Im expecting is

Suppgroup          Value

A                         20

B                         25

I'm not getting the correct output.

• ###### Re: Use of ONLY Function

Can Anybody Explain the use of "Only function" at the time of Loading data ?

for e.g.