topic Re: Crosstable from resident loadSyntax - Field not found in QlikView

Crosstable from resident loadSyntax - Field not found

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Crosstable from resident loadSyntax - Field not found

sunny_talwar — Fri, 07 Oct 2016 17:53:14 GMT

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

Re: Crosstable from resident loadSyntax - Field not found

el_aprendiz111 — Fri, 07 Oct 2016 20:32:11 GMT

Hi,

Re: Crosstable from resident loadSyntax - Field not found

Peter_Cammaert — Sat, 08 Oct 2016 10:01:45 GMT

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.

Re: Crosstable from resident loadSyntax - Field not found

Anil_Babu_Samineni — Sat, 08 Oct 2016 12:57:27 GMT

Have you tried right function

Right(week,2) as fieldname