Currently, I have two different apps that both only show the current snapshot of the data and no historical data. The data from both of these apps comes from Excel files, which are generated in other systems and then processed by me. As of now, I archive the data into folders with the date I pulled the report, and then Qlik Sense reads one main file which gets overwritten every time I process a new report. So I might have 5 folders total. The main folder is called "Report" and then there are subfolders called "2019-10-01", "2019-11-01", "2019-12-02", and "2020-01-02". In the main "Report" folder, the file gets overwritten and the app reads that file, while the archived data goes into the respective subfolder. New subfolders will be created every time a new report is pulled.
App 1 - Updated on a weekly basis. Data from App 2 is used in App 1. About 8 Excel files are loaded into the dashboard. I have to publish a new App monthly with the new data so I can preserve the historical view on the app.
App 2 - Updated on a monthly basis
Instead of having a snapshot view of the most recent data, I also want to display historical data. From my research, QVD files seem to be an effective way of solving my problem. I want this process to be as automatic as possible as well.
1) Are QVDs the best solution for this?
If no, what would be the best method?
If yes, then see the below questions.
2) Do I need to add any additional fields in my data set or standardize anything so I could get the historical view? A field such as "ReportDate". Currently, I have a "ReportDate" in most of the files, but the values are different for the same snapshot. For example, one report has "202001" whereas the other report has "2020-01-02". However, I only care that the data is from January 2020, not the exact date.
3) How can I continuously add in new data to the master QVD file which will contain all the historical data? Can I scan a folder and all subfolders to check to see if that data exists in the QVD?
4) If I have 4 months worth of data in the QVD and I determine that one of the month's data has incorrect values due to the report being wrong, how can I fix those issues in the QVD? Would I need to recreate it entirely or could I delete all the values with that specific "ReportDate" and add the values back?
5) Would it be better for me to just generate a QVD every time pulling from all the subfolders, or create the QVD just update it when new data comes in?
Ideally, the process would go something like this:
1. QVD file created with all historical data
2. Scan subfolders to see if all the data is there
2a. If new data has been added for a new "ReportDate", add it to the QVD.
2b. If historical data has been changed, delete all the historical records for the "ReportDate" it was changed, and then add the changed data for that "ReportDate". Sometimes rows get deleted in the changes or rows get updated and I would rather delete all the data for that "ReportDate" and just repopulate it, unless QVDs can handle that somehow.
This question is fairly large, but I just want to make sure I am going down the right path so I can ask more specific questions in another post. Please let me know if I need to clarify something! Any guidance or assistance would be greatly appreciated!
