Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Row count measure for grouped items

PowerPlay Transformer

Row count measure for grouped items

by  Nunina  Posted    (Edited  )


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.

Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top