RIGHT() / LEFT() Function not working in load script

[#1]

Hi,

I am trying to load a column using the RIGHT() function to only take the 6 right-most characters from it's data.

For some reason, when i try to load the script, the column can't be found.

This is the code i am using for the column:

"Right([Functional loc.],6) as SID"

The following is the column in excel:

Thew error that i'm getting:

I do not want to make a new column in the excel spreadsheet, because i want it to be automated.

Any help would be greatly appreciated.

Matt.

[MayilVahanan]

Hi

I think its not getting error due to right () function.

In ApplyMap, you are using SID field Name which was created in same load. you can't use it in the same load like above.

[JonnyPoole · Accepted solution]

i THINK the error may pertain to the next row which is an applymap function that references SID.  you can't refer to SID in the applymap because its an alias, you need to do that in a preceding load or change the reference from SID to the expression.

[sundarakumar]

SID is a new rename which will be not available for qlikview until the load is complete. It is not the issue where u create SID. U R tryng to use SID in same table pull. which is not supported.

Replace it with right(fieldname,6) for the paricular table alone.

-Sundar

[#5]

Okay, that was the issue. I put them into separate loads, but now the load time has doubled.

Is there any way i can avoid loading the one table twice?

Cheers,

Matt

[JonnyPoole]

I think so ... instead of using SID in the 2nd arguement of applymap() try just putting Right([Functional loc.],6).

Then you can do it in 1 load.

Or maybe a  preceding load to keep your right() expression written in only 1 place

Load

         *

          applymap( <argument1>, SID,  etc...)

     ;

load

     <yourfields>

     Right([Functional loc.],6) as SID

from

<source>

[jemancilla]

I agree with Jonathan Poole , using a preceding load is an elegant way to do it in 1 load!

It looks like you're trying to split [Functional loc.] using '-' as delimiter so you can use Subfield() instead of Right():

Subfield([Functional loc.],'-',2) as SID,