Desktop productivity for business analysts and programmers

Finding the maximum value based on a customer ID

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Finding the maximum value based on a customer ID

Using Enterprise Guide 4.2, I am trying to group data under one value where a customer will have one Customer ID but two locations. I'd like to be able to use the account with the largest balance within a customer id to determine which location to use for all accounts. For example,

Cust ID 1

Acct 1

balance - $1,000

Location - RI

Acct 2

balance -$2,000

Location - DC

I'd like the report to show the customer location as DC based on the fact that for Cust ID x, the account with $2,000 is the largest balance, therefore the location will be DC. The end goal will be to have this grouped on one line.

Thanks for your help.


Accepted Solutions
Solution
‎03-26-2013 03:15 PM
Community Manager
Posts: 2,712

Re: Finding the maximum value based on a customer ID

I agree with - I should have point out in my response that the GROUP/HAVING technique (in the blog post) requires you to "uncheck" automatic grouping in the query builder to get the result you want.

View solution in original post


All Replies
Community Manager
Posts: 2,712

Re: Finding the maximum value based on a customer ID

You should be able to achieve this in the Query Builder, using Grouping with the MAX aggregation.  See this blog post for a similar problem/solution:

HAVING (clause) fun with SAS Enterprise Guide - The SAS Dummy

Chris

Respected Advisor
Posts: 3,124

Re: Finding the maximum value based on a customer ID

Depending on how your data is structured, it could have different approach. What does your table look like?

Haikuo

New Contributor
Posts: 3

Re: Finding the maximum value based on a customer ID

Thank you both.

With respect to the Max, this works as long as I don't have any other data that can cause the results to split again. I have some work to do on my grouping as it seems that I have to do multiple groups to make this work. Perhaps I should do the max earlier in an earlier query as opposed to later?This is one of the reasons I was looking for some sort of computed field.

With respect to the table type. It's a flat file nothing too strange.

Thank you again.

Esteemed Advisor
Posts: 5,202

Re: Finding the maximum value based on a customer ID

With the information you provided it seems you could do this in one step.

Group by on cust id alone (not default grouping as suggested by EG).

Having balance = max(balance).

Data never sleeps
Solution
‎03-26-2013 03:15 PM
Community Manager
Posts: 2,712

Re: Finding the maximum value based on a customer ID

I agree with - I should have point out in my response that the GROUP/HAVING technique (in the blog post) requires you to "uncheck" automatic grouping in the query builder to get the result you want.

New Contributor
Posts: 3

Re: Finding the maximum value based on a customer ID

Thank you all. I was able to make it work based on your advice. One of the issues that I was having was Grouping in the wrong query. Once I figured that out, it works as advertised.

Thank you again for your help.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 278 views
  • 6 likes
  • 4 in conversation