Problem loading data using peek when the column value has single-quotes
I am using the peek function to get values from a XML file. It works perfectly 99% of the cases, but when the value from the XML has single-quotes, the peek function returns everything before the single-quote and ignore the rest.
For example:
In the Excel the node "AcctName" has a record with the value: John's
In that case, the peek function returns me John
If I change the single quotes for double quotes like John"s, it returns correctly: John"s
The problem is I do not want double quotes, I want single quotes.
when you load your XML file into QlikView, you can load the same field twice and replace the single quote by another symbol (for example, an apostrophe) just for the purpose of using it in peek() function. Once you peek the value, you can replace the character back to a single quote. For example:
load
...
AcctName.
replace(AcctName, CHR(37), '`') as AcctName2
...
let vAccName = replace( peek(AcctName2), '`', chr(37))
when you load your XML file into QlikView, you can load the same field twice and replace the single quote by another symbol (for example, an apostrophe) just for the purpose of using it in peek() function. Once you peek the value, you can replace the character back to a single quote. For example:
load
...
AcctName.
replace(AcctName, CHR(37), '`') as AcctName2
...
let vAccName = replace( peek(AcctName2), '`', chr(37))
