In PowerPlay Transformer, is there a way to add a measure to a cube that counts the number of rows for each field, but only the first occurrence of each field, not subsequent fields?
An Impromptu report has been created with a grouping by field, and for each group a '1' is appended as a calculated column. The grand total for the Impromptu report matches perfectly with the database numbers. However, the cube numbers when rolled up are much higher, especially for dimensions with a large number of categories.
Solution:
Impromptu grouping is not carried over into Transformer, so the calculation will be for all categories, inflating the rolled up measures seen in PowerPlay. Use the following technique to work around this problem:
1. In Impromptu, group by the field to be counted.
2. Add a running-count column for each grouping, so that the first occurrence is 1, the second 2, etc. When the next unique value for the field occurs, the count is restarted.
3. Add another calculated column that adds a 1 for the minimum value in the grouping, zeroing out all the others. This way Impromptu is only counting the first occurrence within the group and inserting a zero for all others.
4. Bring this into Transformer using the latter column as the count measure with the Default(Sum) roll up.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.