Field not found error while loading in qlikview

[#1]

Employees:

LOAD

ID,

ID as EMP_ID,

"PMF_KEY",

NAME as Employee_Name,

srm2.full_name as "Manager",

"OBS_UNIT";

SQL

select srm.id ID,srm.unique_name PMF_KEY,srm.full_name NAME,srm2.full_name,pou.id OBS_UNIT

from

niku.srm_resources srm

inner join srm_resources srm2 on srm.manager_id = srm2.user_id

inner join niku.prj_obs_associations poa on srm.id = poa.record_id and table_name like 'SRM_RESOURCES' and srm.is_active=1

inner join niku.prj_obs_units pou on poa.unit_id = pou.id and pou.type_id=5000002

Even though I have the manager in the sql query I still get this error. Can you help me please?

[rwunderlich]

The script you posted doesn't quite match the error message, but from what you posted, the SQL is returning the name "MANAGER" (upper case). That casing is how you need to reference it in the LOAD statement.

-Rob

[#3]

Hi Rob

I am facing a similar sort of problem, the only diff is my Load script is retrieving the data from a CUBE.

When you look into the Cube that dimension and field name is very much present but my Load Script is throwing an exception Field Not Found.

Load Script is attached.

If u look into the script there are two diff Load Statement ..one is creating a qvd named Workforce_Dev.qvd and the other one creates LPI_Dev.qvd.

Both the Load statement is retrieving the records from the same Dimension table Workforce7 and is using more or less same fields as well. Workforce.qvd gets created without any error but when the script tries to execute the Load Statement for LPI it gives an error saying..

Field not found - <[Dim Cost Center].[Cost Center].[Cost Center].[MEMBER_CAPTION]>

SELECT

{

[Measures].[Head Count]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Dim Cost Center].[Cost Center].Children},

{[Dim Cost Center].[Country].Children},

{[Dim Cost Center].[Region].Children},

{[Dim Cost Center].[Sub Region].Children},

{[Dim Country].[Country Desc].Children},

{[Dim Country].[Region Desc].Children},

{[Dim Country].[Sub Region Desc].Children},

{[Dim Data Period].[Fiscal Month].Children},

{[Dim Data Period].[Fiscal Month Of Year].Children},

{[Dim Data Period].[Fiscal Year].Children},

{[Dim Job].[Job Family].Children},

{[Dim Job].[Job Function].Children},

{[Dim Job].[Job Level].Children},

{[Dim Job].[Job Level Group].Children},

{[Dim Job].[Job Level Group Order].Children},

{[Dim Job].[Job Level Order].Children},

{[Dim Labor Strategy].[Center].Children},

{[Dim Labor Strategy].[Location].Children},

{[Dim Worker Type].[Worker Type].Children},

{[Dim Worker Type].[Worker Type Group].&[Employee]},

{[Dim Workforce Status].[Workforce Status].Children},

{[Dim Years of Service].[Years of Service].Children})

ON ROWS

FROM [Workforce7]

WHERE ({ [Dim MRU].[MRU].&[E114]}, { [Dim Workforce Status].[Workforce Status Group].&[Active]})

The field for which it is giving me an error the same field is used for Workforce.qvd as well.

Any sort of guidance to resolve this uncanny problem is highly appreciated.

Thanks.

Vin

[rwunderlich]

I'm not sure exactly what the problem may be, but here is how I would investigate it.

Comment out the LOAD and run just the SQL SELECT. Look in the Document Log to see precisely what fields are being generate.

-Rob

[#5]

@Rob

I did what you suggested and found that the Select statement of Workforce1 is generating  all the fields used in the Select Statement whereas the select statement of LPI1 is only returning one Field that is [Measures].[Head Count].and the Headcount value is null.

But the question still remains WHY so?

If you look in the attached screen shot it shows the number of records retrieved by the respective select statement.

Fields generated by LPI1...Query

! !

Regards

Vin

[#6]

Hi,

Try this

Employees:

LOAD

ID,

ID as EMP_ID,

"PMF_KEY",

NAME as Employee_Name,

[srm.full_name] as "Manager",

"OBS_UNIT";

SQL

select srm.id ID,srm.unique_name PMF_KEY,srm.full_name NAME,srm2.full_name,pou.id OBS_UNIT

from

niku.srm_resources srm

inner join srm_resources srm2 on srm.manager_id = srm2.user_id

inner join niku.prj_obs_associations poa on srm.id = poa.record_id and table_name like 'SRM_RESOURCES' and srm.is_active=1

inner join niku.prj_obs_units pou on poa.unit_id = pou.id and pou.type_id=5000002

[rwunderlich]

I suppose it's all about debugging that SELECT statement now. Perhaps making it piece by piece like the working SELECT. I don't know about this specific domain so I don't have any specific suggestions.

-Rob