topic Problem loading data using peek when the column value has single-quotes in QlikView

Problem loading data using peek when the column value has single-quotes

Wed, 07 Oct 2009 01:34:48 GMT

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.

Any ideas?

Problem loading data using peek when the column value has single-quotes

Oleg_Troyansky — Wed, 07 Oct 2009 02:50:13 GMT

Igor,

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))

(the code is not verified, just an example)

good luck!

Problem loading data using peek when the column value has single-quotes

Thu, 08 Oct 2009 01:30:16 GMT

Thanks for the reply.

It is working now with a single change: It is Chr(39)

Thanks again.