Aggregate data in different Excel rows

[asmithDeveloper]

Hi All,

I'm completely new to this technology.

Problem: I have 2 Excel files from which to extract financial data. File Trade contains transactions and File Balance contains balances. If a record appears in File Trade, its matching balance record will appear in File Balance, but not vice versa. File Trade could have multiple transactions for a particular financial group.

Like so...

File Trade records:

Cusip Advisor BuyOrSell Amount
1 ABC B 1000.00
1 ABC B 50.00
2 123 S 500.00
2 123 S 20.00

File Balance records:

Cusip Advisor AnotherField MarketValue
1 ABC IL 50000.00
2 123 MI 75000.00
3 XYZ OH 30000.00
4 AAA MI 90000.00
5 TTT IL 80000.00

---
What I need to do, Step 1:
---

Work on the File Trade data so that I end up with just 2 records with aggregations of Amount placed in some new column.

Like so...

New File Trade set of records, call it File Trade 2:

Cusip Advisor BuyOrSell Amount
1 ABC B 1050.00
2 123 S 520.00

---
What I need to do, Step 2:
---

I need to merge the data from File Balance and File Trade 2 using Cusip and Advisor as merge keys. This part I know I can accomplish using Merge Join and a Left Join.

---
What I'm not clear about:
---

How do I accomplish the aggregation in Step 1, creating a new temporary data store to use in the Step 2 merge?

I hope I've explained this problem sufficiently well enough.

Thanks in advance for any help!

Arnold

 

[Ankor]

Hi Arnold,
It all depends on how you want to use your SQL Server. You can load both files into the tables, then aggregate and join in one query, load the results into the permanent table, and wipe out data from the fist two entities. I think it's the easiest. Or, sometimes you don't even need to store the results and may want to have two permanent tables with initial data that will be updated every night (week, hour). Then you can have different procedures that will aggregate, merge, pull top values, ext and put the results into the reports.

 

[Catadmin]

Arnold,

As Ankor mentioned, there are several possibilities. Two I can think of off the top of my head: 1) pull your data into a Temp Table and find the aggregates from there using a ForEach container. 2) Use an Execute SQL Task to aggregate your data there and pass the results to a package variable (or two). You would pass the results using the ResultSet tab, not the parameters tab, of the Execute SQL Task.

All you need to start with are two flat file connections to your existing files and then start playing with the Tasks from that point. I recommend using Books Online (BOL) to find out more information about the individual tasks.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"