topic Dynamic update not working in QlikView

Dynamic update not working

Wed, 01 Jun 2016 17:45:05 GMT

Hello people

I have an macro that reads from a .csv file and I need to read line by line and update the data in Qlikview. Here's what I got:

------------------------------------------------------------------------

Sub CarregarArquivo(file)

set oFile = createObject("Scripting.FileSystemObject")

Set dict = CreateObject("Scripting.Dictionary")

Set File = oFile.OpenTextFile (file, 1)

row = 0

Do Until file.AtEndOfStream

line = file.Readline

dict.Add row, line

row = row + 1

Loop

set doc = ActiveDocument

row = 0

For Each line in dict.Items

if row > 0 then

items = Split(line, ";")

Set vIDFiliaisProdutos = ActiveDocument.Variables("vIDFiliaisProdutos")

vIDFiliaisProdutos = items(0) & "_" & items(1) & "_" & items(2) & "_" & items(3)

SET Result = doc.DynamicUpdateCommand ("UPDATE * SET Alocacao = "& items(4) & " WHERE IDFiliaisProdutos = '" & IDFiliaisProdutos & "';")

if Result = false then

MsgBox Result.ErrorMessage

end if

row = row + 1

End Sub

------------------------------------------------------------------------

So far I'm able to read the file and build a command line by line, but when I run it, it stops at the first command exection (in line Set Result...) and the Edit Module window opens. No error is shown, nothing.

Any ideas ?

Thanks in advance !

Josué

Re: Dynamic update not working

tamilarasu — Wed, 01 Jun 2016 19:05:48 GMT

Hi Josue,

Could you post a sample qvw file along with your csv file? It would be easy for me to check (by tomorrow morning) the issue.

Re: Dynamic update not working

tamilarasu — Wed, 01 Jun 2016 19:26:44 GMT

Meantime, you can check whether the update statement ( I suspect something wrong here) is correct or not by adding messagebox like below.

SQL_String = "UPDATE * SET Alocacao = "& items(4) & " WHERE IDFiliaisProdutos = '" & IDFiliaisProdutos & "';"

Msgbox SQL_String

Also, I guess you entered wrong variable name in the where clause.

WHERE IDFiliaisProdutos = '" & vIDFiliaisProdutos & "';"

Hope this helps.

Re: Dynamic update not working

santiago_respane — Wed, 01 Jun 2016 19:32:00 GMT

Hi Josue,

at first sight your syntax looks fine, please your app with dummy data in order to help you fixing your issue.

One important thing to consider is that for the command update to work you must be part of the Qlikview Administrators group.

Please let me know if this helps.

Kind regards,

Re: Dynamic update not working

Wed, 01 Jun 2016 22:01:46 GMT

Hello Tamil !!

Yes, the name of the variable was the problem !! Thank you very much !!

Also, I found out that I need to enable Dynamic Data Update in Document Properties -> Server.

Thank you again, and Santiago, for all help !!