topic Re: RIGHT() / LEFT() Function not working in load script in QlikView

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

Fri, 19 Sep 2014 03:53:54 GMT

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.

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

MayilVahanan — Fri, 19 Sep 2014 03:57:58 GMT

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.

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

JonnyPoole — Fri, 19 Sep 2014 04:00:20 GMT

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.

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

sundarakumar — Fri, 19 Sep 2014 04:00:34 GMT

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

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

Fri, 19 Sep 2014 04:24:35 GMT

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

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

JonnyPoole — Fri, 19 Sep 2014 04:28:58 GMT

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

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

from

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

jemancilla — Fri, 19 Sep 2014 05:32:19 GMT

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,