topic Field not found even though i am loading it in QlikView

Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 11:56:58 GMT

Getting field not found error even though it is on the line above?

"Field not found - <Group Code>"

All from the first tab:

asi:

LOAD

COMPANY,

S_ID,

NAME_VALUE AS [Group Code],

DESCRIPTION AS [Group Name]

FROM

(qvd);

Details:

load

date(MakeDate(Year,Period,1),'MMM-YY') as Month,

if([Group Code]=[Berth code],[Group Name],'Missing') as [Man acc group],

LOAD *,

if ([Source] = 'ACCRUALS', 'Accruals','Invoices') as [Inv or acc],

if ([Source] = 'ACCRUALS' and IsNull([Product Description Temp]), 'Accruals',[Product Description Temp]) as [Product Description],

left([Year period],4) as Year,

right([Year period],2) as [Period]

;

LOAD

SalesInvAndAccruals.CUSTOMER_ID as [Customer ID],

SalesInvAndAccruals.CUST_NAME as [Customer name],

SalesInvAndAccruals.YEAR_PERIOD_KEY as [Year period],

date(SalesInvAndAccruals.VOUCHER_DATE,'DD-MMM-YY') as [Invoice Date],

SalesInvAndAccruals.COST_CENTRE as [Berth code],

SalesInvAndAccruals.CC_DESC as [Berth name],

SalesInvAndAccruals.PRODUCT as [Product Code],

SalesInvAndAccruals.PROD_DESC as [Product Description Temp],

SalesInvAndAccruals.QUANTITY as [Quantity],

SalesInvAndAccruals.NET_AMOUNT as [Value],

SalesInvAndAccruals.SOURCE as [Source],

dual(date(makedate(year(date(SalesInvAndAccruals.VOUCHER_DATE,'DD-MMM-YY')),num(month(date(SalesInvAndAccruals.VOUCHER_DATE,'DD-MMM-YY')))),'MMM-yyyy'),

year(date(SalesInvAndAccruals.VOUCHER_DATE,'DD-MMM-YY')) * 100 + num(month(date(SalesInvAndAccruals.VOUCHER_DATE,'DD-MMM-YY')))) as PayMonthYear

FROM

[C:\Users\test\Desktop\qvd\SalesInvAndAccruals.qvd]

(qvd)

where SalesInvAndAccruals.ACCOUNT = '3300'

and SalesInvAndAccruals.YEAR_PERIOD_KEY > '201310'

and SubStringCount(upper(SalesInvAndAccruals.OBJ_DESC),'JETTY DUES') =0

and not(Match(SalesInvAndAccruals.COST_CENTRE,'402','403')) ;

DROP Fields [Source],[Product Description Temp];

Re: Field not found even though i am loading it

MayilVahanan — Fri, 18 Mar 2016 12:01:20 GMT

You need to join / mapping Details and asi table to bring the Group Code in Details table before use that field.

Could you provide the full script or sample file to achieve your requirement

Re: Field not found even though i am loading it

Gysbert_Wassenaar — Fri, 18 Mar 2016 12:01:38 GMT

Details:

load

date(MakeDate(Year,Period,1),'MMM-YY') as Month,

if([Group Code]=[Berth code],[Group Name],'Missing') as [Man acc group],

*;

If that's the entire load statement then you're missing a FROM xxx or Resident xxxx. You're not telling Qlikview where to load the data from.

Re: Field not found even though i am loading it

maxgro — Fri, 18 Mar 2016 12:02:38 GMT

in the Details load you miss a source (can be a resident table, a qvd, an excel, a database, a preceding load)

And this source should have all the fields you are referencing

- Year

- Period

- [Group Code]

- [Berth code]

- [Group Name]

Details:

load

date(MakeDate(Year,Period,1),'MMM-YY') as Month,

if([Group Code]=[Berth code],[Group Name],'Missing') as [Man acc group],

*; // from???? resident ???

Re: Field not found even though i am loading it

Chanty4u — Fri, 18 Mar 2016 12:03:33 GMT

If(Match([Group Code], [Berth code],[Group Name],'Missing',) as [man acc group]

Re: Field not found even though i am loading it

Clever_Anjos — Fri, 18 Mar 2016 12:03:37 GMT

"Details:

load

date(MakeDate(Year,Period,1),'MMM-YY') as Month,

if([Group Code]=[Berth code],[Group Name],'Missing') as [Man acc group],

*;"

It´s supposed to have something below this, would you mind sharing ?

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:11:49 GMT

full script added

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:11:54 GMT

full script added

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:12:00 GMT

full script added

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:12:10 GMT

full script added

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:12:29 GMT

missing some brackets there i think

Re: Field not found even though i am loading it

Chanty4u — Fri, 18 Mar 2016 12:14:37 GMT

try to replace that group code to something else as group1 or lyk dat and reload again?

Re: Field not found even though i am loading it

MayilVahanan — Fri, 18 Mar 2016 12:15:41 GMT

Try like this

asi:

LOAD

COMPANY,

S_ID,

NAME_VALUE AS [Group Code],

DESCRIPTION AS [Group Name]

FROM

(qvd);

MappingGroupName:

Mapping LOAD [Group Code],

[Group Name]

Resident asi;

Details:

load

date(MakeDate(Year,Period,1),'MMM-YY') as Month,

Applymap('MappingGroupName', [Berth code],'Missing') as [Man acc group],

Load * your remaining script;

Hope it helps

Re: Field not found even though i am loading it

MayilVahanan — Fri, 18 Mar 2016 12:16:59 GMT

Applymap definition from help

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:19:56 GMT

the one in the load or the one in the if statement, or both?

Re: Field not found even though i am loading it

Chanty4u — Fri, 18 Mar 2016 12:24:12 GMT

NAME_VALUE AS group1,

and give group1 evry where

Re: Field not found even though i am loading it

matthewp — Fri, 18 Mar 2016 12:29:11 GMT

Field not found - <group1>

NAME_VALUE AS group1,

and

if(group1=[Berth code],[Group Name],'Missing') as [Man acc group],

Re: Field not found even though i am loading it

Ralf-Narfeldt — Fri, 18 Mar 2016 12:29:34 GMT

You loaded Group Code and Group Name in the asi table, but when you are loading Details, these fields are not in scope as they are not in the table you are currently loading.

Instead of if([Group Code]=[Berth code],[Group Name],'Missing')

you may want to do a LookUp instead, where you check if Berth code is found in asi.[Group Code], and if it does, return

asi.[Group Name]

LookUp('Group Name', 'Group Code', Berth code, 'asi')

LookUp ‒ QlikView