Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
I have found a few aticles. thank you. If anyone else has any experience with write-backs, please let me know. Thanks.
Hi, dcaldwell
dcaldwellCould you please highlight this topic by using some example or turorial/example. I will appreciate it
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)
next
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