Aggregate (SUM) a column and then use the Result?

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

What about using an Execute SQL task in the control flow to put that value into a variable; then you can use that variable in the data flow and perform the calculation using a derived column. This thread explains how to run queries against an excel file: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1

|||

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

|||Hi Jamie,

I understand the flow:

Load Data
Calculate the Sum
Calculate new columns based on the sum (mostly count_value/total to get the rate)
Insert the final data into database.

What I cannot figure out is how to do the sum as an aggregate and then use it to do the computation.

Fawad
|||Hi Jamie,

I understand the flow that I require:

Import data from Excel
Sum the one column
Use this sum to calculate new column (mostly rates)
Load in the database

What I cannot figure out is how to use the result of the sum from the aggregate task.

Fawad
|||

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad
|||

Fawad wrote:

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad

Right. So Rafael's suggestion is exactly how you should attempt to do this.

-Jamie

|||

Fawad,

My sugestion was to use an Execute SQL task (control flow) to get the SUM value into a variable; it would be a query like Select Sum(yourcolumn) from [YourExcelSheet]. this step has to be done before the dataflow. Then the dataflow would have that value avilable and it could be used in a derived column.