topic Re: Use of ONLY Function in QlikView

Use of ONLY Function

tresB — Tue, 31 Aug 2010 11:55:39 GMT

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

johnw — Wed, 01 Sep 2010 00:08:15 GMT

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.

Use of ONLY Function

tresB — Wed, 01 Sep 2010 06:03:04 GMT

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

Use of ONLY Function

johnw — Wed, 01 Sep 2010 18:30:07 GMT


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

Fri, 05 Nov 2010 15:58:59 GMT

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

Use of ONLY Function

johnw — Fri, 05 Nov 2010 18:14:22 GMT

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

Mon, 08 Nov 2010 09:15:31 GMT

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

johnw — Mon, 08 Nov 2010 17:14:44 GMT

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

IAMDV — Thu, 21 Apr 2011 13:13:33 GMT

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

Many thanks

Re: Use of ONLY Function

Anonymous — Fri, 12 Jul 2013 10:58:50 GMT

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

jonathandienst — Fri, 12 Jul 2013 12:29:54 GMT

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

Anonymous — Mon, 15 Jul 2013 06:13:38 GMT

Thanks Jonathan, I moved my question here: http://community.qlik.com/thread/86889

Re: Use of ONLY Function

Mon, 09 Jun 2014 15:36:40 GMT

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

aparnanair1405 — Thu, 16 Apr 2015 14:53:09 GMT

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

Wed, 18 Nov 2015 10:36:09 GMT

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

for e.g.

LOAD

Only([Entry Type]) as Entry Type,

[Posting Date]

From Value_Entry.qvd(qvd);

Re: Use of ONLY Function

tresB — Wed, 18 Nov 2015 10:54:01 GMT

It would load a [Entry Type] value only if it has a single value. Otherwise, the field would load nothing.