Fawad


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




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

Rafael Salas


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







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

Jamie Thomson

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







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

Fawad

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




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

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




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

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




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

Jamie Thomson

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






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

Rafael Salas

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.