topic problem with values that contain SEMICOLONs - bug? in QlikView

problem with values that contain SEMICOLONs - bug?

wizardo — Wed, 10 Feb 2010 08:15:31 GMT

hi all

i have a problem when i need to pass values from fields and they contain a semicolons ";"

the api function DynamicUpdateCommand expect a string with the this format when you want to use it to add records to an existing QVlogical table:

INSERT INTO <tablename> (-list of fields names-) VALUES (comma seperatd list of values)

all thos must be passed to the function in a form of ONE string.

Result = ActiveDocument.DynamicUpdateCommand ("INSERT INTO * (Country, City) VALUES (DK, Copenhagen), (NO, Oslo)")

now assume instead of the value "Oslo" i need to insert the value --> "O;slo" (with a semicolon)

when i try this for real the qlikview hangs

any ideas?

problem with values that contain SEMICOLONs - bug?

Wed, 10 Feb 2010 10:52:30 GMT

I believe the ';' is being seen as a terminator - I dont know how QV talks to the target database but certainly ';' is a terminator in SQL Procedural Language (SPL). Widely used within QV as well

You need to either strip them out perhaps by creating a seperate field in the script and use 'purgechar' on it or maybe do something with 'replace' to change it to the ansi value 'replace(fieldname, ';', chr(59)'

Regards,

Gordon

problem with values that contain SEMICOLONs - bug?

wizardo — Wed, 10 Feb 2010 13:09:01 GMT

hi Gordon

thanks for your answer

i have already tried replacing the ";" with chr(59) but since the string is probebly parsed in someway before it is posted by qlikview i guess it just substitue it back to the ";" so it gives same effect

and since the need is to preserve the original value then obviously i won't be able to do just purge it or replace it with any other character

so again i ask:) a bug??

Wizardo