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

    Use of ONLY Function

    Tresesco B

      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
          John Witherspoon

          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
              Tresesco B

              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
                  John Witherspoon

                   


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



                       

                      Thanks in advance for your time and help



                        • Use of ONLY Function
                          John Witherspoon

                          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 Big Smile

                              I'm still getting my head round QlikViews syntax Confused

                              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
                                  John Witherspoon

                                  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.

                                • Re: Use of ONLY Function
                                  Aparna Nair

                                  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
                            Abhishek Pansotra

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