Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

DynamicUpdateCommand fails with multiple tables

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.

1 Solution

Accepted Solutions

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Contributor III
Contributor III

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!

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Contributor III
Contributor III

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.