We need to use a PU function where we want to pull up to a key stored in the same table as where we want to do the SUM over (in our case):
PU_SUM(DOMAIN_TABLE('table1'.'key1'),'table1'.'field1')
However, key1 occurs multiple times in the table, resulting in the sum of field1 being repeated for each occurrence. How can we only get a result once per key? We tried adding DISTINCT but that didn't work...
Hello,
I think INDEX_ORDER with GROUP worked for that quite well.
Thanks for your reply @klaudia.krol12 ! I learned a new function thanks to you :)
My initial assumption that key1 was repeated appeared false. The real cause was a bug in the boxplot component.
Hi there,
To clarify this a bit further: The generated DOMAIN_TABLE always only contains distinct values, otherwise it would not be possible to aggregate to it via PU function in the first place. Usually, the values appear repeated when you join the PU result with the original table, e.g. by adding the dimension "table1"."key1" to your OLAP table (or whichever component you use).
To avoid this join, you can use e.g. PU_FIRST to retrieve the value for the dimension not from your "table1", but from your DOMAIN_TABLE: PU_FIRST(DOMAIN_TABLE('table1'.'key1'),'table1'.'key1'). By using this as your dimension, you don't introduce the join between the DOMAIN_TABLE and "table1", and therefore you don't get any duplicate values.
There is also an example in our documentation which describes this kind of issue as well as appropriate solutions.
Best
David
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
