I have 4 monthly reports that I run which feed into multiple apps. Each of the reports either has a "ReportDate" or "AcctPeriod" column to identify the date.
ReportDate Example: 2019-12-19
AcctPeriod Example: 201912
Reports:
A.xlsx (30 columns)
B.xlsx (25 columns)
C.xlsx (20 columns)
D.xlsx (40 columns)
Folder Structure:
Main (This is where the apps read the data from)
2019-12-02 (Archive of historical data)
2019-12-09 (Archive of historical data)
Current Process
The current process is that I have one file for each app that has the current month's data only, no historical. I read the data from Excel (.xlsx) files. Each month, I place the Excel file into an archive folder with the date of that report. Then, I overwrite the main file with the new month's data which my apps read.
Desired Result
I would like to store the data for each of the reports in their on respective QVD file so I can track historical data as well based on the "ReportDate" or "AcctPeriod".
Assistance Needed - Proposed Solution
For each report, I would like to create a QVD file that will either append OR delete and append data. Sometimes I have to update the reports if there are issues so if the "ReportDate" or "AcctPeriod" exists in the QVD file and any of the rows are different, then delete all the rows for that "ReportDate" or "AcctPeriod" and append the updated data to the QVD for that same "ReportDate" or "AcctPeriod". If the "ReportDate" or "AcctPeriod" does not exist in the QVD file, then append the data to the QVD.
If there are better ways of doing this, I am definitely open to suggestions. This would allow me to track month over month changes instead of a snapshot view of the current month.
