maximum profit from overall branch

mcs_dineshm · Post by **mcs_dineshm** » Thu Sep 27, 2007 8:33 am

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.....

Maveric · Post by **Maveric** » Thu Sep 27, 2007 8:38 am

Max function in sql would help.

mcs_dineshm · Post by **mcs_dineshm** » Thu Sep 27, 2007 8:50 am

No,i need in datastage

Maveric · Post by **Maveric** » Thu Sep 27, 2007 9:02 am

Sort data on profit field in descending order and take only the first row from the output.

Ronetlds · Post by **Ronetlds** » Thu Sep 27, 2007 9:38 am

Maveric wrote:Sort data on profit field in descending order and take only the first row from the output.

Try maximum function in aggregator stage

ray.wurlod · Post by **ray.wurlod** » Thu Sep 27, 2007 4:49 pm

DataStage was never intended to be a reporting tool. Why are you trying to use the wrong tool?

Ronetlds · Post by **Ronetlds** » Thu Sep 27, 2007 5:23 pm

My guess is management decree. They paid for it, ergo you must use it. I've actually worked in a situation where the push was to convert EVERY server job to EE even though we were chugging through a whopping 20,000 rows max per job. Feeling nauseous yet?

ray.wurlod · Post by **ray.wurlod** » Thu Sep 27, 2007 7:15 pm

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.

mcs_dineshm · Post by **mcs_dineshm** » Sun Sep 30, 2007 12:38 am

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..

mcs_dineshm · Post by **mcs_dineshm** » Sun Sep 30, 2007 12:48 am

hi ray can u tel me wat does a reporting tool does actually, and wat the datastage should do, as u said i'm using a wrong tool... I'm having a query to find the maximum profitable branch out of five branches, if its possible to execute this query in sql why not in datastage..

ray.wurlod · Post by **ray.wurlod** » Sun Sep 30, 2007 2:30 am

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.

chulett · Post by **chulett** » Sun Sep 30, 2007 8:19 am

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..

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.

Or, as noted, do this in your BI / reporting tool (if you have one) which is exactly what function *it* was purchased for.