Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

frankrudolf
New Contributor III

Why don't SUBSTRING work in the LOAD

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

1 Solution

Accepted Solutions

Re: Why don't SUBSTRING work in the LOAD

Try,

=mid(Name,Index(Name,'(',1)+1, Index(Name,'(',1)-Index(Name,'(',1)+4) as name

Include this condition in LOAD statement instead SQL SELECT

14 Replies
datanibbler
Esteemed Contributor

Re: Why don't SUBSTRING work in the LOAD

Hi,

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

HTH

frankrudolf
New Contributor III

Re: Why don't SUBSTRING work in the LOAD

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

datanibbler
Esteemed Contributor

Re: Why don't SUBSTRING work in the LOAD

Don't know that one.

What does it do?

frankrudolf
New Contributor III

Re: Why don't SUBSTRING work in the LOAD

seaching for a pattern in a field e.g.

PATINDEX('(%',Name))

is searching for % in Name and returning the position

Re: Why don't SUBSTRING work in the LOAD

Try,

LOAD *,

mid(Name,2,4) as New_Name;

SQL SELECT *

from database ;

Re: Why don't SUBSTRING work in the LOAD

It's index() function in QlikView

datanibbler
Esteemed Contributor

Re: Why don't SUBSTRING work in the LOAD

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
Honored Contributor III

Re: Why don't SUBSTRING work in the LOAD

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

Not applicable

Re: Why don't SUBSTRING work in the LOAD

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

Community Browser