topic What is the problem in my VBScript? (Dynamic Update) in QlikView

What is the problem in my VBScript? (Dynamic Update)

Sun, 23 Aug 2009 06:57:23 GMT

Dear All,

I load a table as below

I declared a variable vLowSkill for input value.

And i also wrote a script for update FunctionTypeScale.Low$ column.

The Script will update the selected key with rule:

FunctionTypeScale.Low$=strValue when FunctionTypeScale.Low>strValue

otherwise FunctionTypeScale.Low$=FunctionTypeScale.Low


sub vbLowSkillUpdate
 dim nbrSelected, strValue, query, sqlerror,condition, newValue
 ' Get selected Value
 set possibleValues = ActiveDocument.Fields("FunctionTypeScaleKey").GetPossibleValues

 ' Get new value
 SET objSource = ActiveDocument.Variables("vLowSkill")
 strValue = objSource.GetContent.String

 set docprop = ActiveDocument.GetProperties
 docprop.EnableDynamicDataUpdate=true
 ActiveDocument.SetProperties docprop

 nbrSelected=possibleValues.Count
 if (nbrSelected>0) then
 for i=0 to nbrSelected-1
 condition="([FunctionTypeScaleKey]='"& possibleValues.item(i).text & "')"
 newValue=" if([FunctionTypeScale.Low]>"&strValue&","&strValue&",[FunctionTypeScale.Low]) "
 query="UPDATE FunctionTypeScale SET [FunctionTypeScale.Low$]="& newValue & " WHERE "& condition
 set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
 if (sqlerror = false) then
 msgbox(query&chr(10)&"Update has error!")
 exit sub
 end if
 next
 end if
end sub

Everything seems to be OK but nothing is updated. Script always display error message.

I don't know why?

Who can help me to solve it?

What is the problem in my VBScript? (Dynamic Update)

Mon, 24 Aug 2009 20:15:21 GMT

Hello,

what kind of error do you have ?

Philippe

What is the problem in my VBScript? (Dynamic Update)

Mon, 24 Aug 2009 22:35:11 GMT

Hi Philippe,

Thanks for your attention.

When the script is run, it display the error as below:

This message is generated by program. It means that no thing is updated.

TMTHAI

What is the problem in my VBScript? (Dynamic Update)

Tue, 25 Aug 2009 00:05:29 GMT

Hello,

You should set the FunctionTypeScale.low value outside of the SQL by itself. Then you would be able to change your sql and remove that if sentence in it.

if (FunctionTypeScale.Low > 3 then

FunctionVar = 3

else

FunctionVar = FunctionTypeScale.Low

end if

Your SQL should look like .

Update FunctionTypeScale set FunctionTypeScale.Low$ = FunctionVar Where Func.....

And that should work

Philippe