Write Back to Database via ETL process (using CSV or XML)
I previously posted a sample budgeting application that built an XML document from the budget data and submitted that to an ASP page (much like using a web service) when the user clicked on the button. While this works fine for smaller amounts of data saved, it can seem slow for larger amounts of data as the user is left waiting for the updates to be processed in real time. A more scalable approach is to ‘disconnect’ the process of updating the database from the user experience, by simply exporting the changes to a file and letting a separate ETL process handle those files. That is what I have created in this example.
The ETL logic is encapsulated in a .vbs file, and will process any number of CSV files (from multiple users) in sequential order. This is because the CSV files are given a name in a timestamp format.
There are also many other enhancements to the original app I posted. These are:
a) It now only submits entries for update that have actually been changed
b) It uses the very cool INPUSUM() function & a hierarchy, which lets you edit budgets at any level in the hierarchy. (Thx to jbb for this!).
c) The username is recorded against updated rows in the database
e) Popup text objects are used in the UI rather than alerts via the msgbox() function, as the latter dont work in the AJAX client
f) traffic lights are used o show you which rows have been edited and need to be submitted
g) A table shows the files that are queued in the directory awaiting processing by the ETL
Finally, it is much prettier than the last application I posted, which was put together pretty quickly. I hope you find it useful.
One warning regarding inputfields, which this solution utilises. Inputfield values are stored in the .SHARED file on the server. A unique set of values is stored for each user, so dont expect user's to see each others edited values. This can get confusing, so ensure you design the app so that there is a column showing the "Saved" values, and a separate column showing "My Edited" values. It also helps to add a button to allow the user to clear their inputfield values. Also, if there are many inputfield rows and many users then the SHARED file will get large and the inputfield table can get too slow to use. In these cases you need something like Kliqplan or KliqTable, which can be found via Google or in QlikMarket.
PLEASE NOTE: This is an example application only, and is simply an illustration of the capabilities of macros within QlikView and the ability to "Write back" to the data source. It is not supported in any way and is offered "AS IS" for illustration purposes only.