Crosstable from resident loadSyntax - Field not found

[#1]

I want to use Crosstable from resident load.

The reason to do this is - we have week as "2016-35" but I want to read only 35.

However, because this is an incremental file and we don't have fixed column so can not specify the field name and use subfield function.

Hence used the below script:

Se_Sale_New1:

LOAD *

resident SE_Sale where Sino=$(vMaxline2) ;

DROP table SE_Sale;

SE_Final:

CrossTable(Week, Data)

LOAD *,

SubField(Week,'-',2) as Week_SE

resident Se_Sale_New1;

drop table Se_Sale_New1;

But I am getting an error "Field not found <Week>". Which is genuine but would like to know the alternate approach to achieve my requirement.

[sunny_talwar]

Week is not present? Not sure I am understand, do you have a sample you can share with expected output?

[el_aprendiz111]

Hi,

[Peter_Cammaert]

I guess that the Week field that you are using in the LOAD column specification is not present in the input table Se_Sale_New1. Therefor, you cannot use it as a source column in LOAD expressions. The reason is simple: although the CROSSTABLE step is specified before the LOAD, it is actually executed after the LOAD. As are all prefixes (JOIN, KEEP etc.)

In addition to that, even a regular CROSSTABLE LOAD is known to cause all sorts of incompatibilities when you start getting creative. In the interest of manageability (& code readability), I would suggest to pull your manipulations apart into two successive LOADs, just like fer fer suggested.

[Anil_Babu_Samineni]

Have you tried right function

Right(week,2) as fieldname