topic Dynamic update in QlikView

Dynamic update

Tue, 21 Jul 2009 02:51:12 GMT

Hi All,

In script, I load products.qvd:

Products:
LOAD ProductID,
ProductDescription,
StartDate,
EndDate,
CostPrice
FROM
..\..\DataSources\Products.qvd
(qvd);
------------------------------------------------
Now, i want to create a button when click on this button
the CostPrice will be update with formula
Ex: CostPrice=CostPrice*1.15

I tried with subroutine:

sub update
ActiveDocument.DynamicUpdateCommand("UPDATE Products SET CostPrice=CostPrice*1.15")
end sub

but there are no change

How can i solve this problem?

Dynamic update

Anonymous — Tue, 21 Jul 2009 03:45:17 GMT

I guess is what you really need is not a button but a variable (in input box or slider). If the variable name is Var, the expresions in your chart / tables will include
CostPrice*Var

Dynamic update

Tue, 21 Jul 2009 04:02:58 GMT

Hi Michel,

You are right. It means that CostPrice=CostPrice*Var and Var may be a input or slider.

But my problem is I want to update data in qv file and I can not update column CostPrice in table Product"

Regards,

Thai

Dynamic update

Anonymous — Tue, 21 Jul 2009 06:09:20 GMT

Thai,

I don't know of any way of updating data other than reload. Probably it's not what you need, but you can use varibale (or constant 1.15 if you wish) in the load script:
Products:
LOAD
ProductID
..
CostPrice,
CostPrice*$(Var) as NewCostPrice,
...
FROM...
If you want to keep the same field name:
DELETE FIELD CostPrice FROM Products;
JOIN (Products) LOAD
ProductID,
NewCostPrice as CostPrice
RESIDENT Products;
So your button should assign value to Var, run reload, and save. Simpe enough, except it make take long time depending on your data.

Dynamic update

Wed, 22 Jul 2009 00:53:26 GMT

Hi Michael,

Thank you for your help that is ok.

In my case, I want only change products which were selected.

My problem is I didn't turn on dynamic update attribute of document

The problem is solved as below:

sub vbCostPriceUpdate
dim NoSelected, answer,strValue
' Get product list which are selected
set possibleValues = ActiveDocument.Fields("ProductID").GetPossibleValues

NoSelected=possibleValues.Count
if (NoSelected>0) then

' Set DynamicDataUpdate=true for current document
set curDoc = ActiveDocument.GetProperties
curDoc.EnableDynamicDataUpdate=true ' <---- Here is my problem when I can not UPDATE
ActiveDocument.SetProperties curDoc

' Get new costprice from variable
SET objSource = ActiveDocument.Variables("vNewCost")
strValue = objSource.GetContent.String

' Update each selected row in table Products
for i=0 to NoSelected-1
query="UPDATE Products SET CostPrice="& strValue & " WHERE ProductID='"& possibleValues.item(i).text & "'"
set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
if (sqlerror = false) then ' send message when error
MsgBox sqlerror.ErrorMessage
exit sub
end if
next
end if
' save all change in current document
ActiveDocument.Save
end sub

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

I want to say thanks again.

Regards,

Thai

Dynamic update

mongolu — Mon, 03 Aug 2009 12:40:07 GMT

i saw that the WHERE clause is a must.

meaning, you must put the " WHERE CostPrice=CostPrice" in your query, at the end.

it worked for me.

Dynamic update

Wed, 24 Mar 2010 03:35:20 GMT

Hi Thai,

Does your application work on the server. I have a similar application. I am getting a "Macro parse failed. Functionality was lost." when the DynamicUpdateCommand is executed. The application runs fine locally on my computer.

Any ideas what causing this error?

Thanks

Dynamic update

partenope — Wed, 30 Jun 2010 11:32:43 GMT

Hi,

when the application runs on QVServer, with Ajax, plug-in or other client, I know that there is a special Server licence to install...

bye