topic Re: DynamicUpdateCommand fails with multiple tables in QlikView

DynamicUpdateCommand fails with multiple tables

bluishjoe — Thu, 14 Jun 2012 07:58:40 GMT

Hi, I was using DynamicUpdateCommand this way:

dim stmt
stmt = "UPDATE * SET CHOOSE = 'S' WHERE TIPDOC='o' and NUMORD=123456 and NUMCAR='987654'
set result = ActiveDocument.DynamicUpdateCommand(stmt)
 if result = false then
  MsgBox "Error with DynamicUpdateCommand. " & result.ErrorMessage
end if

It always worked when I had only one table (ORDINE), with some lookup tables.

But when I added more tables it started to fail (result is false and unfortunately result.ErrorMessage is empty, so no clue is given).

I attach my Table Viewer, with some explanation.

I someone can help me, I would appreciate so much, because I can found very few docs about DynamicUpdateCommand in the net.

Re: DynamicUpdateCommand fails with multiple tables

jonathandienst — Thu, 14 Jun 2012 08:05:29 GMT

I suspect that the problem is related to the fact that TIPDOC, NUMCAR, NUMORD etc are now key fields (actually part of the Syn Key).

I suggest that you make aliases for those fields that exist only in table ORDINE.

Hope that helps

Jonathan

Re: DynamicUpdateCommand fails with multiple tables

bluishjoe — Thu, 14 Jun 2012 08:14:25 GMT

The problem is that I need ORDINE, ORDINE_FO, VARIABILI to be linked together, because I need to filter on ORDINE based on selections on the other tables.

Maybe should I build a single table (ORDINE) with everything inside, using SQL joins?

Otherwise DynamicUpdateCommand will not work?

Thanks!

Re: DynamicUpdateCommand fails with multiple tables

jonathandienst — Thu, 14 Jun 2012 08:25:32 GMT

I dont think the link is a problem, but I suspect the dynamic update does not work when the fields are key fields. That's why I suggest that you make aliases for these fields and apply the dynamic update using the aliases. You could possibly collapse everything into one table, but how well that works depends on your data structure. Creating the aliases does not affect your model structure.

To create the aliases, modify your load script, something like this...

ORDINE:

LOAD

....

NUMORD,

NUMORD As duNUMORD,

RIGORD,

RIGORD As duRIGORD,

TIPDOC,

TIPDOC As duTIPDOC,

NUMCAR,

NUMCAR As duNUMCAR

....

FROM ....

Then modify the dynamic update to use the fields with the du.... prefix

.... WHERE duTIPDOC = 'o' .... etc

Hope that helps

Jonathan

Re: DynamicUpdateCommand fails with multiple tables

bluishjoe — Thu, 14 Jun 2012 09:37:07 GMT

You're great!! Now it works, thank you so much.

Only a note: in the script fields are case-insensitive, but in DynamicUpdateCommand they are case-sensitive and they refer to the fieldnames loaded in-memory by QlikView, which become all upper-case (you can see it for instance in Table Viewer). So in DynamicUpdateCommand always use upper-case fields names, otherwise it will throw an error.