Getting error "Field Not Found"

Kartik2 — Tue, 02 Jan 2024 10:53:10 GMT

I've copied the whole script of my Qlikview to Qlik sense,
but a specific field called [BF-1.A.Amout]is getting an error saying that it can't be found in Qlik sense,but the same script runs successfully in Qlikview.
I have to change the field name of [BF-1.A.Amount] to [BF-22.A.Amount] then the data loads successfully , but the values are not matching with that of Qlikview

Re: Getting error "Field Not Found"

anat — Tue, 02 Jan 2024 10:57:09 GMT

if possible can you share your load script?

seems like you are using Qualify in your script..

Re: Getting error "Field Not Found"

Kartik2 — Tue, 02 Jan 2024 11:34:21 GMT

here it is the field i'm referring to [BF-1.A.Amount] can be found in BFF table below

TRACE '//~~~~~~~~~~~~~~~~~~~Cycle 4.1 Started~~~~~~~~~~~~~~~~~~~~~~~//';

//~~~~~~~~~~~~~~4 GL for CE and interest for int allocation~~~~~~~~//

//-----Cycle 4 Sender Company code data filter-------

BUKRSFilter:

LOAD * INLINE [

BUKRSFilter,

2200-ELC1

1500-FRG1

];

QUALIFY *;

UNQUALIFY %KeySalesTotalYear,%KeySalesMonth, %KeySalesMonthPlant, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec;

LOAD

Only(RRCTY) as RRCTY ,

Only(RVERS) as RVERS ,

Only(RYEAR) as RYEAR ,

Sum(Amount) as Amount ,

Only(BUKRS) as BUKRS ,

Only(Month) as Month

//-Only([Acct Code]) as [Acct Code]

Group by

RRCTY ,

RVERS ,

RYEAR ,

BUKRS ,

Month ;

//- [Acct Code] ;

LOAD

// RVERS&'-'&RYEAR as %KeySalesTotalYear,

// RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,

// RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,

RBUSA ,

RRCTY,

RVERS,

RYEAR ,

Amount ,

// 'Z001' as BUKRS ,

BUKRS,

Month ,

[Acct Code]

Resident GLT0_2 Where not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and %FlagC4VELPlantNum_C4=1 and %FlagC4VELINTRGLNum=1 and RRCTY=0;

Join (A)

LOAD * INLINE [

Acct Code1, RRCTY1, RVERS1, %FlagReport, FlagCycle, Factor

GM01150, 0, 1, BS, S, 1

GE02054, 0, 1, PL, S, -1

GM01150, 0, 1, BS, R, -1

GE02054, 0, 1, PL, R, 1

];

UNQUALIFY*;

//~~~~~~~~~~~~~Sending Company Code~~~~~~~~~~~~~~~//

C4Sending:

Concatenate(GLT0_2)

LOAD

'C4.11' as %FlagCycle,

1 as %FlagC4VELPlantNum_C4,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,

A.RRCTY as RRCTY,

A.RVERS as RVERS ,

A.RYEAR as RYEAR ,

INLFED.Factor*A.Amount as Amount,

A.BUKRS as BUKRS ,

A.Month as Month ,

[INLFED.Acct Code1] as [Acct Code]

Resident A Where [INLFED.FlagCycle] ='S';

//~~~~~~~~~~~~~Receiveing Company Code~~~~~~~~~~~~~~~//

C4Receiveing:

Concatenate (GLT0_2)

LOAD

'C4.11' as %FlagCycle,

// 1 as %FlagC4VELPlantNum_C4,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,

A.RRCTY as RRCTY,

A.RVERS as RVERS ,

A.RYEAR as RYEAR ,

INLFED.Factor*A.Amount as Amount,

'Z001' as BUKRS ,

A.Month as Month ,

[INLFED.Acct Code1] as [Acct Code]

Resident A Where [INLFED.FlagCycle] ='R';

//~~~~~~~~~~~~~Sending Company Code~~~~~~~~~~~~~~~//

Concatenate (GLT0_2)

LOAD

'C4.12' as %FlagCycle,

// 1 as %FlagC4VELPlantNum_C4,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,

If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,

A.RRCTY as RRCTY,

A.RVERS as RVERS ,

A.RYEAR as RYEAR ,

INLFED.Factor*A.Amount as Amount,

'Z001' as BUKRS ,

A.Month as Month ,

[INLFED.Acct Code1] as [Acct Code]

Resident A Where [INLFED.FlagCycle] ='S';

//~~~~~~~~~~~~~Fiter data for 22 Plant and for Capital Employed GL(934)~~~~~~~~~~~~~~~~~//

UNQUALIFY*;

BFilter:

LOAD

BUKRS&'-'&[Acct Code] as %KeyC4VELPlantRec

Resident GLT0_2 Where %FlagC4CEALLPlantGLNum=1 and %FlagC4VELPlantNum_C4=1;

LOAD

// 'All' as Flag,

RVERS&'-'&RYEAR as %KeySalesTotalYear,

RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,

RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,

RBUSA ,

RLDNR ,

RRCTY ,

RTCUR ,

RVERS ,

RYEAR ,

Amount ,

BUKRS ,

DRCRK ,

HSLVT ,

Month ,

[Acct Code]

Resident GLT0_2 Where Exists (%KeyC4VELPlantRec,BUKRS&'-'&[Acct Code]) and not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and RRCTY=0 and %FlagCycle<>'C4.11';

//Fiter data for 4 Plant and for Capital Employed GL(31)

BFilter4:

LOAD

BUKRS&'-'&[Acct Code] as %KeyC4VEL4PlantRec

Resident GLT0_2 Where %FlagC4CE4PlantGLGLNum=1 and %FlagC4VELPlant4Num_C4=1;

Concatenate(B)

LOAD

// '4' as Flag,

RVERS&'-'&RYEAR as %KeySalesTotalYear,

RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,

RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,

RBUSA ,

RLDNR ,

RRCTY ,

RTCUR ,

RVERS ,

RYEAR ,

Amount ,

BUKRS ,

DRCRK ,

HSLVT ,

Month ,

[Acct Code]

Resident GLT0_2 Where Exists (%KeyC4VEL4PlantRec,BUKRS&'-'&[Acct Code]) and not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and RRCTY=0 and %FlagCycle<>'C4.11';

QUALIFY*;

UNQUALIFY %KeySalesTotalYear,%KeySalesMonthPlant, %KeySalesMonthTotal, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec, %JoinKey;

B1:

LOAD

// Flag ,

RBUSA ,

RLDNR ,

RRCTY ,

RTCUR ,

RVERS ,

RYEAR ,

BUKRS ,

DRCRK ,

Month ,

[Acct Code],

HSLVT,

Amount

Resident B;

DROP Table B;

UNQUALIFY *;

//~~~~~~~~~~~~YTD Joins~~~~~~~~~~~~~~~//

B2:

LOAD

1 as B1.MonthNew,

If(B1.Month=1, B1.Amount,Null()) as Amount1

Resident B1 Where B1.Month=1;

Concatenate(B2)

LOAD

2 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2, B1.Amount,Null()) as Amount2

Resident B1 Where B1.Month=1 or B1.Month=2;

Concatenate(B2)

LOAD

3 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3,B1.Amount,Null()) as Amount3

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3;

Concatenate(B2)

LOAD

4 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4, B1.Amount,Null()) as Amount4

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4;

Concatenate(B2)

LOAD

5 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5, B1.Amount,Null()) as Amount5

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5;

Concatenate(B2)

LOAD

6 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6, B1.Amount,Null()) as Amount6

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6;

Concatenate(B2)

LOAD

7 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7, B1.Amount,Null()) as Amount7

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7;

Concatenate(B2)

LOAD

8 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8, B1.Amount,Null()) as Amount8

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8;

Concatenate(B2)

LOAD

9 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9, B1.Amount,Null()) as Amount9

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9;

Concatenate(B2)

LOAD

10 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10, B1.Amount,Null()) as Amount10

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10;

Concatenate(B2)

LOAD

11 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11,B1.Amount,Null()) as Amount11

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11;

Concatenate(B2)

LOAD

12 as B1.MonthNew,

If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11 or B1.Month=12,B1.Amount,Null()) as Amount12

Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11 or B1.Month=12;

DROP Table B1;

NoConcatenate

B1:

LOAD

B1.RVERS&'-'&B1.RYEAR as %KeySalesTotalYear,

B1.RVERS&'-'&B1.RYEAR&'-'&B1.MonthNew as %KeySalesMonthTotal,

B1.RVERS&'-'&B1.RYEAR&'-'&B1.MonthNew&'-'&B1.BUKRS as %KeySalesMonthPlant,

If(B1.Month=1,Alt(B1.HSLVT,0)+B1.Amount,B1.Amount) as AmountNew

Resident B2;

DROP Table B2;

QUALIFY*;

UNQUALIFY %KeySalesTotalYear,%KeySalesMonthPlant, %KeySalesMonthTotal, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec, %JoinKey;

B2:

LOAD

%KeySalesMonthPlant,

Only(%KeySalesMonthTotal) as %KeySalesMonthTotal,

// Sum(B1.Amount) as SalesPlantTotal_Old,

Sum(AmountNew) as SalesPlantTotal

Resident B1 Group by %KeySalesMonthPlant;

//All Plant Sum

Left Join (B2)

B3:

LOAD

%KeySalesMonthTotal as %KeySalesMonthTotal,

// Sum(B1.Amount) as SalesTotal_Old,

Sum(AmountNew) as SalesTotal

Resident B1 Group by %KeySalesMonthTotal;

//Weightage avg Plant wise:

BF:

LOAD

B2.SalesPlantTotal,

B3.SalesTotal,

Num((B2.SalesPlantTotal/B3.SalesTotal)) As SalesWeghtAvg,

// B2.SalesPlantTotal_Old,

// B3.SalesTotal_Old,

// Num((B2.SalesPlantTotal/B3.SalesTotal)) As SalesWeghtAvg_Old,

%KeySalesMonthPlant,

%KeySalesMonthTotal

Resident B2;

DROP Table B2;

Left Join (BF)

LOAD

%KeySalesMonthTotal,

Sum(A.Amount) As A.Amount

Group by %KeySalesMonthTotal;

LOAD

A.RVERS&'-'&A.RYEAR&'-'&A.Month as %KeySalesMonthTotal,

A.Amount

Resident A Where [INLFED.FlagCycle] ='R' and INLFED.Factor=1;

//DROP Field %KeySalesMonthTotal;

BFF:

LOAD

%KeySalesMonthPlant,

SubField(%KeySalesMonthPlant,'-',3) as Month,

BF.B2.SalesPlantTotal,

BF.B3.SalesTotal,

BF.SalesWeghtAvg,

// BF.%KeySalesMonthTotalTemp,

// %KeySalesMonthTotal,

[BF-22.A.Amount],

(BF.SalesWeghtAvg*[BF-22.A.Amount]) as MonthlyRecevingTotalAmt

//- [BF-1.A.Acct Code]

Resident BF;

DROP Table BF;

UNQUALIFY*;

Join (BFF)

LOAD * INLINE [

Acct Code, RRCTY, RVERS, %FlagReport, FlagCycle, Factor

GM01150, 0, 1, BS, C4, -1

GE02054, 0, 1, PL, C4, 1

];

Concatenate (GLT0_2)

LOAD

'C4.12' as %FlagCycle,

1 as %FlagC4VELPlantNum_C4,

If([Acct Code]='GE02054',1,0) as %FlagC4VELINTRGLNum,

If([Acct Code]='GE02054',1,0) as %FlagC10PLGLNum,

0 as RLDNR,

SubField(%KeySalesMonthPlant,'-',2) as RYEAR,

SubField(%KeySalesMonthPlant,'-',3) as Month,

SubField(%KeySalesMonthPlant,'-',4) as BUKRS,

Factor*BFF.MonthlyRecevingTotalAmt as Amount,

[Acct Code] as [Acct Code],

RRCTY as RRCTY,

RVERS as RVERS

Resident BFF;

DROP Tables A, BFF, B1, BFilter, BFilter4, BUKRSFilter;

TRACE '//~~~~~~~~~~~~~~~~~~~Cycle 4.1 Finished~~~~~~~~~~~~~~~~~~~~~~~//';

Re: Getting error "Field Not Found"

anat — Tue, 02 Jan 2024 12:04:37 GMT

can you try with [BF.A.Amount] then check the values

Re: Getting error "Field Not Found"

Kartik2 — Tue, 02 Jan 2024 12:17:39 GMT

Not working again same error [BF.A.Amount]field not found

topic Re: Getting error "Field Not Found" in App Development

Getting error "Field Not Found"

Re: Getting error "Field Not Found"

Re: Getting error "Field Not Found"

Re: Getting error "Field Not Found"

Re: Getting error "Field Not Found"