0

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

null
    • Lead Consultant Advanced Analytics
    • Michael_Daun
    • 1 mth ago
    • Reported - view

    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.

    • Principal Customer Solutions Consultant
    • Mark_Oldfield.1
    • 1 mth ago
    • Reported - view

    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.

Content aside

  • Status Answered
  • 1 mth agoLast active
  • 2Replies
  • 31Views
  • 3 Following