2 Replies Latest reply: Nov 15, 2016 5:04 AM by Gysbert Wassenaar RSS

    Calculated dimension using rank and concat

    Tanya Ruppell

      Hello,

       

      This is a follow up to Variable expansion, quotes and equal sign.

       

      Here is my data:

      LOAD * INLINE [

        location, id

        siteA, 1

        siteB, 2

        siteC, 3

        siteD, 4

      ];

       

      If I want a dimension that is a list of all locations, I can either define it in the dimension: ='$(=Concat(location, ', '))'

      Or I can create a variable SET vTest= =Concat(location, ', ') and the dimension defined as =vTest

       

      Now, I want to limit how many locations I see to the top two. I know that I can use rank to limit my set like this: =if(aggr(rank(min(id)),location)<=2,location) , but I can't seem to find a way to combine this with Concat to use in a calculated dimension

       

      The result I'm looking for is siteC, siteD. (Order is not important)

       

      Also, is there a way to do this using set expression instead of if statement? My real data is very large and I expect performance will be an issue.

       

      Thanks,

      Tanya