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

[sasiparupudi1]

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

[qlikviewwizard]

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.

[stigchel]

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

[Anonymous]

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.

[Kushal_Chawda · Accepted solution]

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