topic Dealing with apostrophe in load statement in QlikView

Dealing with apostrophe in load statement

chrisbrain — Thu, 18 Aug 2011 10:25:18 GMT

I have a load script running inside a for loop, something like this:

for i=0 to $(noRows)-1 // loop through every row

let textRaw = peek('Search_text',$(i),'TwitterConnector_Search'); // get the value for "text" field on each row

Table1:

LOAD

'$(textRaw)' as Search_text,

status as sentiment_status,

FROM //.....

The problem is that some of the values contain apostrophes, meaning the expanded script (when viewed in the log file) ends up looking something like this:

LOAD

'''text with apostrophe in' as Search_text,

status as sentiment_status

FROM

// ....

Resulting in a:

Syntax error, missing/misplaced FROM:

Error.

Is there any way I can deal with this and load the textRaw into Table1 with apostrophes intact?

Thanks!

Dealing with apostrophe in load statement

matt_crowther — Thu, 18 Aug 2011 11:00:11 GMT

Chris,

This may well fall in to the 'Cowboy Workaround' catergory but it could work:

Replace the apostrophes (chr(39)) with another non-used but standard character - for instance '^' (chr(94)) before the load so as to stop them causing problems before reversing the process after the load by replacing chr(94) with chr(39) to re-instate your apostrophes.

As I said "Yeee-Haaw".

Hope that helps a little,

Matt - Visual Analytics Ltd

Dealing with apostrophe in load statement

chrisbrain — Fri, 19 Aug 2011 06:58:45 GMT

Haha thanks - but wouldn't this mean I didn't have the correct text in my Table1?

I have actually done a work around now to use a separate id parameter to link the table to the rest of the schema, so it's now something like:

let textRaw = peek('Search_text',$(i),'TwitterConnector_Search'); // get the value for "text" field on each row let id = peek('Search_text',$(i),'TwitterConnector_id');

Table1:

LOAD

'$(id)' as Search_id,

status as sentiment_status,

FROM //..... Note textRaw is url encoded and used in load request here which is why we need it.

It would have been better if I could link on the text but I think this is a safer solution!

Dealing with apostrophe in load statement

swuehl — Fri, 19 Aug 2011 09:25:04 GMT

Chris,

I am curious because I also have encountered similar problems.

I don't really understand your solution, for id, you query a field also named Search_text, but from a different table, right? Does this gives you an id, then?

One more idea:

Why do you think a text based link would be better? I would guess from a performance side, a numerical should be probably better. If you doubt that your id is as unique as your search text, you may create a separate hash value using e.g. hash256() function.

Regards,

Stefan