Is there a way to create a new dimension from a previous load?
I have a list of accounts that are overdue and each week and there are new overdue accounts and some accounts are corrected. The goal is to have close to zero overdue accounts if possible. Therefore, I want to capture my week to week or month to month overdue accounts to see information on which employees are trending in the right direction. Unfortunately my data source only provides which accounts are overdue at a given time when the data is downloaded so when I load new data on to Qlik I lose the previous weeks totals. I know I could create a separate excel file where I manually track the overdue account totals from the previous reports and upload the data in Qlik but it would be great if this could be done by Qlik with a resident table/drop table script when the new data is loaded.
Below is the current script. I would like to keep the previous reported Contract Status (Overdue, Due, Current, Final) totals only by Organization by Business Sector so I can create a line chart.
[Raw Data]: LOAD [Contract ID] as Key, [Contract Number], [Order Number], [Contract Status], Date(Date#([Contract Due Date], 'MM/DD/YYYY') ) AS [Contract Due Date], [Business Sector], Date(Date#([Contract Completion Date], 'MM/DD/YYYY') ) AS [Contract Completion Date], [Organization], [DCC/CCC], [DIV] From [lib://AttachedFiles/ContractStatus.xlsx] (ooxml, embedded labels, table is [Raw Data]);
Re: Is there a way to create a new dimension from a previous load?
A simple solution would be load the data every week and after reload save them into filename_weekno_year.qvd in a folder path. (weekno and year will be dynamic) if you want data for multiple weeks then every week one qvd will be created, if the requirement is just to have last week data have a static name in qvd(fliename_lastweek.qvd) which will be overwritten every week.
To comapre the value with the values that comes next week reload the new data and last week qvd, do your comaprision.
Hopefully this helps you.Let me know if you didn't understand the logic here.