Help with Set Expression usage in Bar Chart

Tue, 15 Jul 2014 16:25:13 GMT

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

topic Help with Set Expression usage in Bar Chart in Water Cooler

Help with Set Expression usage in Bar Chart