Why don't SUBSTRING work in the LOAD

[Anonymous]

I was under the assumption that regular SQL scripts could be used in the load scripts, but I'm struggling with getting this LOAD script to work.

It seems like when I add a SUBSTRING to the script e.g.

LOAD *;

SQL SELECT substring(Name,2,4)

from database ;

do I get an error saying

OLEDB read failed

SQL SELECT substring(Name,2,4)

from database

Why is that ?

/Frank

[datanibbler]

Hi,

it does work - only QlikView syntax is slightly different. It's SUBFIELD().

HTH

[Anonymous]

Thanks, in that case, how about SQL PATINDEX is it ant different syntax in qlikview ?

[datanibbler]

Don't know that one.

What does it do?

[Anonymous]

seaching for a pattern in a field e.g.

PATINDEX('(%',Name))

is searching for % in Name and returning the position

[Kushal_Chawda]

Try,

LOAD *,

mid(Name,2,4) as New_Name;

SQL SELECT *

from database ;

[Kushal_Chawda]

It's index() function in QlikView

[datanibbler]

Well, the INDEX function would do that. I guess there are similar functions, too.

Just browse them in the help_file - all the STRING_functions are on one page which is quite annoying whenever you look for a specific one, only for learning about all the possibilities and the syntax it's practical.

[sasiparupudi1]

Probably a wildmatch which can be used to search for patterns in the field values.

[#10]

Better load normally do modification for particular field in precceding load or resident load than drop the previous table