writeback user changes/field-modifications from qlikview back to QVD and SQL database

Anonymous — Mon, 01 Nov 2010 21:10:35 GMT

Has anyone had any experience using Qlikview as a tool that will allow users to modify a limited number of displayed fields (from a pick list) such as changing a vendor status from Active to Inactive or vendor classification (Meat to Starch) and having that change write back to the qvd and the source SQL table? Any suggestion or blogs would be appreciated.

writeback user changes/field-modifications from qlikview back to QVD and SQL database

johnw — Mon, 01 Nov 2010 21:32:54 GMT

I believe I've seen examples on the forum doing this with macros, but I haven't ever done it myself. So it's possible, but I'm not much help.

writeback user changes/field-modifications from qlikview back to QVD and SQL database

Anonymous — Tue, 02 Nov 2010 01:02:04 GMT

I have found a few aticles. thank you. If anyone else has any experience with write-backs, please let me know. Thanks.

writeback user changes/field-modifications from qlikview back to QVD and SQL database

Wed, 01 Jun 2011 23:23:27 GMT

Hi, dcaldwell

dcaldwellCould you please highlight this topic by using some example or turorial/example. I will appreciate it

writeback user changes/field-modifications from qlikview back to QVD and SQL database

Thu, 02 Jun 2011 15:39:45 GMT

I don't have know how to exacly what you want but I've done something similar (but certainly not as good as what you want). Maybe you can take what I've done and improve it to fit your needs. I took most of this from an example in the share qlikviews section about dice and edited some of it to make it fit my needs so I don't want to take all the credit (not that I've really done anything amazing or anything).

Basically I've got a .qvw that loads from a single .qvd. I want to add a field called "Checked_Flag" which you can change with the value of a variable (vFlag) using DynamicUpdateCommand in a macro. The problem is that when you update a field the actual qvd isn't actually changed (regardless of whether or not that field exists in the qvd). It's important to note that Checked_Flag isn't a field in the actual qvd, it is one that I want to add in there. I would bet you could do this same type of thing for fields already in the qvd but off the top of my head I'm not sure.

Let's say I want to insert a value of 1 into the Checked_Flag field. First I select the Account Number (or multiple account numbers) that I want to change the value for, type 1 into the vFlag input box, and I click a button that runs the sub Insert macro (shown below). Now i must hit a second button that saves the table into a text file (sub exportTable, which I guess I could add to the same button I hit earlier if I wanted) to save the changes.

However, all that does is save the updated table into a text file and I'm loading the actual data from a qvd (which is still unchanged). At the end of my script I added....

LEFT JOIN LOAD DISTINCT

Account_Number_esiid_Key, //this is just the ID for each customer

Checked_Flag

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

At the end of the script I could save the .qvd but I didn't actually do that so my original data source remains unchanged throughout. This certainly doesn't solve your problem but I think it's a little similar so hopefully it gives you some direction.

sub Insert

vKey = ActiveDocument.GetVariable("vKey").GetContent.String

vFlag = ActiveDocument.GetVariable("vFlag").GetContent.String

v = saveData(vKey,vFlag)

end sub

function saveData(vKey,vFlag)

set vKey = ActiveDocument.Fields("Account_Number_esiid_Key").GetPossibleValues 'Account_Number_esiid_Key is just the unique ID for each customer

for i = 0 to vKey.Count - 1

sql = "UPDATE NinetyPlus SET Checked_Flag = " & "'" & vFlag & "'" & " WHERE Account_Number_esiid_Key = " & "'" & vKey.Item(i).text & "'"

ActiveDocument.DynamicUpdateCommand(sql)

ActiveDocument.Clear

ActiveDocument.Variables("vSaveFlag").SetContent "1",true

end function

sub exportTable

ActiveDocument.Clear

set tb = ActiveDocument.GetSheetObject("TB01") 'TB01 is identical to the entire qvd

tb.Export "C:\Documents and Settings\QV\exportTable.txt",","

ActiveDocument.Variables("vSaveFlag").SetContent "0",true

end sub

topic writeback user changes/field-modifications from qlikview back to QVD and SQL database in QlikView

writeback user changes/field-modifications from qlikview back to QVD and SQL database

writeback user changes/field-modifications from qlikview back to QVD and SQL database

writeback user changes/field-modifications from qlikview back to QVD and SQL database

writeback user changes/field-modifications from qlikview back to QVD and SQL database

writeback user changes/field-modifications from qlikview back to QVD and SQL database