Data Model - summarize column, unique row
Hello,
I am running into a problem when developing my data model. I have two rows of data:
| ID | AMT | CREDITS | COUNT_REG |
| UID1 | 24775 | 18.00 | 1.00 |
| UID1 | -1286 | 18.00 | 1.00 |
I want to be able to have the amt column add up but only count one row. another words I want the data to be:
| UID1 | 23489 | 18.00 | 1.00 |
But what's happening is i get:
| UID1 | 23489 | 36.00 | 2.00 |
When I developed in SAS EG, I was able to use summary statistics. I've tried remove duplicates, distinct, etc. Nothing seems to be working.
Help would greatly be appreciated!!!!
2 replies
-
Hi Tabitha,
in other words: You want to have different aggregation functions for these measure columns, i.e. SUM for AMT, and (e.g.) AVERAGE for CREDITS and COUNT_REG. This can be configured in the definition of the measures within the model. -
Hi
I would see this more as a logical data model challenge initially, looking at your data I think it's represents the below:-
the left-hand side represents what you currently have, and the right hand represents the entity normalized into two facts and a dimension.
The normalised model on the right should be the one to be modelled as this will be able to service the requirements you need. Normalizing data into a star or snowflake is the best approach when designing a data model.