maximum profit from overall branch
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
maximum profit from overall branch
I am having five branches and my query is to caluclate the maximum profitable branch.By using aggregator,br_id as group key i am getting 5 rows with maximum profit of each branch,but i need only one row containing the most profitable branch among all the branches to be displayed.. Can anyone solve my query.....
dineshm
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Push back against illogical decrees. Always. Otherwise you're asking for grief. Explain the downside(s), and get them to put their signature on a document you've prepared containing these explanations and the assertion that they want things done their way irrespective. At least then you can shift the blame when that becomes necessary. And it will.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
Hi friends, i cant use aggregator stage as i'm having five different branches so it'l give five branch details as i'm gouping by branch id. but i need only the branch with which is most profitable alone. And also i cant use sorting and get the first data alone as there wil be a problem if two branches have the same maximum profit..
dineshm
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Nobody said it was not possible in DataStage. The point was that you are using a sledgehammer to repair a watch. If it can be done in SQL, do it in SQL. DataStage can issue the SQL, if you must, but it would be better using a reporting tool, either a database client tool or a business intelligence tool.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
So? In either SQL or DataStage you'll need to 'aggregate' to compute your value and then sort descending to get the branch with the highest value. Constrain it to take only the first row post-sort if you always need to pick one, or use stage variables to constrain your output to all of the first rows with the same max value.mcs_dineshm wrote:Hi friends, i cant use aggregator stage as i'm having five different branches so it'l give five branch details as i'm gouping by branch id. but i need only the branch with which is most profitable alone. And also i cant use sorting and get the first data alone as there wil be a problem if two branches have the same maximum profit..
Or, as noted, do this in your BI / reporting tool (if you have one) which is exactly what function *it* was purchased for.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers