Field not found even though i am loading it

[matthewp]

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];

[matthewp]

missing some brackets there i think

[Chanty4u]

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

[MayilVahanan · Accepted solution]

Hi

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

[MayilVahanan]

Hi

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

[matthewp]

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

[Chanty4u]

NAME_VALUE AS  group1,

and give group1 evry where

[matthewp]

Field not found - <group1>

NAME_VALUE AS group1,

and

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

[Ralf-Narfeldt]

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