Scripting Issue - Field not found error

[#1]

Hi Team,

I am new to QlikView and trying to learn scripts and formulas.

I am stuck at below situation wherein system shows 'Feild Not found' error. Details are mentioned below

//************************************************************

Ultimate_Sector_Name:

LOAD * INLINE [

    Sector Name, Final Sector Name

    ENERGY CHEMICAL& UTILITIES (ECU), ECU_900

    FINANCIAL SERVICES (FS), FS

    GENERAL SERVICES (GS), Others

];

Data:

LOAD  [Employee BU],

         [Employee BU Name],

         [Sector Name],

         [Project Region],

         recno() as Srno

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD  *,

      IF(([Employee BU] ='10037' OR [Employee BU] ='10036'),

      IF([Project Region]='GB'and [Final Sector Name] ='FS','FS GB',)) as Project_R_N

    RESIDENT Data;

//************************************************************

If I load the above scripts without the 'Final' table script, it works smoothly. Feild 'Final Sector Name' gets populated correctly.

But when I try to load 'Final' table script, then system shows an error message Feild 'Final Sector Name' not found.

Could you please advise how should I resolve the issue.

Thanks in advance

Shinu

[swuehl]

You are referencing field  'Final Sector Name' in your final table load, but this field is not part of the Input table 'Data'.

Use an ApplyMap() or JOIN to get the field into table Data.

Don't join - use Applymap instead

[sunny_talwar · Accepted solution]

The field Final Sector Name doesn't reside in Data table (the table from which you are doing a resident load from).... Since it is not in Data table, you cannot use it. One option is to join Data into Ultimate_Sector_Name, but I fail to understand where exactly would you want to join? Right now it seems like a Cartesian Product between the two tables, is that what you want?

Ultimate_Sector_Name:

LOAD * INLINE [

    Sector Name, Final Sector Name

    ENERGY CHEMICAL& UTILITIES (ECU), ECU_900

    FINANCIAL SERVICES (FS), FS

    GENERAL SERVICES (GS), Others

];

Join (Ultimate_Sector_Name)

LOAD  [Employee BU],

        [Employee BU Name],

        [Sector Name],

        [Project Region],

        recno() as Srno

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD  *,

      IF(([Employee BU] ='10037' OR [Employee BU] ='10036'),

      IF([Project Region]='GB'and [Final Sector Name] ='FS','FS GB',)) as Project_R_N

    RESIDENT Ultimate_Sector_Name;

[migueldelval]

Hi Shinu,

I think that you need to make a correct "If expression". IF("Condition", "THEN", "ELSE")

If you attach an example, I could help you.

Regards

Miguel del Valle

[#5]

Thank-you so much Stefan and Sunny . You helped to understand scripting much better now.