0 Replies Latest reply: Jul 15, 2014 12:25 PM by Percy Dobbelsteyn RSS

    Help with Set Expression usage in Bar Chart

      Hi,

       

      Can someone please help me with constructing a proper Bar Chart.

      I'm trying to use a set-expression, instead of using a really REALLY SLOW sum(if(...

       

      Here's my data:

       

      [code]

      QUALIFY *;

      Table1:

      LOAD * INLINE [

      ResourcePoolName, Capacity

      Pool A, 100

      Pool B, 200

      Pool C, 300

      ];

       

       

      Table2:

      LOAD * INLINE [

      PoolName, Demand, Person

      Pool A, 50, Mirjam

      Pool A, 25, Percy

      Pool A, 10, Jerry

      Pool B, 175, Jerry

      Pool C, 100, Mirjam

      Pool C, 100, Percy

      Pool C, 100, Jerry

      ];

      [/code]

       

      Table2.PoolName field has a document trigger to also select the same Table1.ResourcePoolName. I have very good reasons for this, so please don't give any answers that the two fields should be named the same.

       

      Dimension in bar chart:

      Table2.PoolName

       

      Two expressions:

      1. Demand:  sum(Table2.Demand)

      2. Capacity: Difficulties here!!!

       

      I've tried:

      2. sum(if(Table2.PoolName = Table1.ResourcePoolName, Table1.Capacity, 0))   [error here because of counting doubles AND VERY VERY SLOW]

      2. sum({$<Table1.ResourcePoolName = P(Table2.PoolName)>} Table1.Capacity)  [wrong]

      2. sum({$<Table2.PoolName = P(Table1.ResourcePoolName)>} Table1.Capacity) [wrong too]

      2. sum({$<Table1.ResourcePoolName = {$(=Table2.PoolName)} >} Table1.Capacity) [wrong]

      2. sum({$<Table2.PoolName= {$(=Table1.ResourcePoolName )} >} Table1.Capacity) [wrong]

       

      I've heard the set expression is faster than the IF statement, but I can't get it to work.

      Any help is greatly appreciated.

       

      I have other reasons why these are not coupled

       

      Thanks,

       

      Percy