topic Re: Scripting Issue - Field not found error in QlikView

Scripting Issue - Field not found error

Mon, 30 Jan 2017 10:24:15 GMT

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

Re: Scripting Issue - Field not found error

swuehl — Mon, 30 Jan 2017 10:46:13 GMT

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

Re: Scripting Issue - Field not found error

sunny_talwar — Mon, 30 Jan 2017 10:48:48 GMT

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;

Re: Scripting Issue - Field not found error

migueldelval — Mon, 30 Jan 2017 10:53:22 GMT

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

Re: Scripting Issue - Field not found error

Mon, 30 Jan 2017 11:12:20 GMT

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