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
