Qlik Community

QlikView Documents

Documents for QlikView related information.

Write Back to Database via ETL process (using CSV or XML)

lms
Contributor III

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.
Labels (1)
Attachments
Comments
Not applicable

Hi Lee,

This is great!  I just had to update the connection string in the script as I am running 64bit version of Access.

One minor bug I found was that the InputSum function added cents to the Proposed values, but only whole dollars are written to the database. Therefore, after the reload, the traffic light still shows red and it is not obvious why unless you change the number format to show decimals.

Michael

lms
Contributor III

Hi Michael

I am glad you find it useful. Thanks for the feedback. If I get a chance I will have a look and see why it is cutting off the cents.

Not applicable

It probably just comes down to the Number format selected in the Budget Updates table.  That would determine what is getting written to the database.

Also, you could modify your expression for the Update traffic light to ignore decimals.

alexis
Contributor III

Excellent - very helpful indeed

lms
Contributor III

Thanks Alexis. I just updated the post with a mild warning though...

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.

MVP
MVP

However, it is also possible to write back data during script load via database connections..

alexis
Contributor III

Can you show us how Ralf? I'm not clear about your suggestion

BR
Alexis

mellerbeck
Contributor II

You mention making a button for clearing inputfield values, how do you do this?

lms
Contributor III

Create a macro like this one:

sub ClearInputFields

set field = ActiveDocument.Fields("MyBudget")

field.ResetInputFieldValues 0

set field = nothing

end sub

Then call the macro with an action on a button.

MVP
MVP

@Alexis: For instance

SQL insert into mytable (mycol) values (1);

You have to connect (open) database in read and write mode to execute DML or DDL statements.

With the JDBC Connector you will get a result set if a DML statement was executed with the update count.

Version history
Revision #:
1 of 1
Last update:
‎05-03-2012 01:52 AM
Updated by:
lms