topic Re: Why don't SUBSTRING work in the LOAD in QlikView

Why don't SUBSTRING work in the LOAD

Anonymous — Wed, 22 Jul 2015 11:44:28 GMT

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

Re: Why don't SUBSTRING work in the LOAD

datanibbler — Wed, 22 Jul 2015 11:46:06 GMT

Hi,

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

HTH

Re: Why don't SUBSTRING work in the LOAD

Anonymous — Wed, 22 Jul 2015 11:51:49 GMT

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

Re: Why don't SUBSTRING work in the LOAD

datanibbler — Wed, 22 Jul 2015 11:52:41 GMT

Don't know that one.

What does it do?

Re: Why don't SUBSTRING work in the LOAD

Anonymous — Wed, 22 Jul 2015 11:54:26 GMT

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

Kushal_Chawda — Wed, 22 Jul 2015 11:55:26 GMT

Try,

LOAD *,

mid(Name,2,4) as New_Name;

SQL SELECT *

from database ;

Re: Why don't SUBSTRING work in the LOAD

Kushal_Chawda — Wed, 22 Jul 2015 11:56:01 GMT

It's index() function in QlikView

Re: Why don't SUBSTRING work in the LOAD

datanibbler — Wed, 22 Jul 2015 11:56:39 GMT

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.

Re: Why don't SUBSTRING work in the LOAD

sasiparupudi1 — Wed, 22 Jul 2015 11:57:56 GMT

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

Re: Why don't SUBSTRING work in the LOAD

Wed, 22 Jul 2015 11:58:29 GMT

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

Re: Why don't SUBSTRING work in the LOAD

sasiparupudi1 — Wed, 22 Jul 2015 12:00:50 GMT

Index function returns the position of a substring in the string and it has to have an exact match

Re: Why don't SUBSTRING work in the LOAD

qlikviewwizard — Wed, 22 Jul 2015 12:03:39 GMT

Hi use

Left() or Right() functions.

left( s , n )

Substring of the string s. The result is a string consisting of the first n characters of s.

Example:

left('abcdef',3) returns 'abc'.

left(Date, 4) where Date = 1997-07-14 returns 1997.

For a more complicated example, see the index function below.

right( s , n )

Substring of the string s. The result is a string consisting of the last n characters of s.

Example:

right('abcdef',3 ) returns 'def'.

right( Date,2 ) where Date = 1997-07-14 returns 14.

Re: Why don't SUBSTRING work in the LOAD

stigchel — Wed, 22 Jul 2015 12:10:42 GMT

Qlikview functions do not work within a SQL Select statement, these can be used in a preceding load and/or resident load. For the sql I think it's giving an error because you have not defined a fieldname e.g.

SQL SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial

From ...

Re: Why don't SUBSTRING work in the LOAD

Anonymous — Wed, 22 Jul 2015 12:27:35 GMT

Thanks,

I'm almost there I think, but can't I write

SQL SELECT Name ,mid(Name, index(Name,'('),4)

from database;

To get a read from Name starting where there is a '(' and 4 positions.

Re: Why don't SUBSTRING work in the LOAD

Kushal_Chawda — Wed, 22 Jul 2015 12:44:30 GMT

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