I am working on an HR project that receives an export from our SAP system weekly. It delivers the weekly totals that were input into the General Ledger for a specific account.
Company Code G/L Fiscal Year Local Currency Amount in LC 0020 4544000 2012 USD 575.00 0020 4544000 2012 USD 252.70 0020 4544000 2012 USD 89.75 0020 4544000 2012 USD 44.00 2012 Total 86,422.58 0020 4544000 2013 USD 2,000.00 0020 4544000 2013 USD -2,000.00 0020 4544000 2013 USD 35.00 0020 4544000 2013 USD 35.00 2013 Total 110,979.23 0004544000 Total 197,401.81 Grand Total 197,401.81
(I removed several rows to save space)
What I need is for this to be simply the Sum of the Year.
Fiscal Year Amount in LC 2012 86,422.58 2013 110,979.23
And it needs to continue on well into 2016 or beyond.
The file I am importing is an .xlsx that is exported from an SAP system. I have had to get a consultant to refine the export as the first couple were unusable.
I currently have an SSIS package that imports the data, runs it through an aggregation before exporting it to a staging table. However, the Already Sumed totals is causing a duplication in the staging table. If there is a way to run a derived column to remove the data then that would also help.
The aggregation data looks like this. Fiscal Year Amount in LC NULL 394803.62 <- Needs to be removed 2012 86422.58 <- Correct 2013 110979.23 <- Correct 2012 Total 86422.58 <- Duplication, Needs to be removed 2013 Total 110979.23 <- Duplication, Needs to be removed
Thank you for any help you can provide.
Brian D. Brubaker Business Intelligence Analyst Viega LLC