topic Re: Field Not Found in QlikView

Field Not Found

Tue, 14 Apr 2015 15:15:41 GMT

I'm trying to do a load but get field not found message. What could I be doing wrong???

Here is the error message:

Field not found - <key_field>

Left Join(BM_Temp)

load [key_field],

sum(if(Value_Ovr<>0,Value_Ovr,Value)) as SHIP_QTY

Resident BM_Temp

where Measure = 'Shipment Qty'

Group by key_field

Here is the script:

bench_override:

LOAD

text("T$ITEM") as ITEM,

"T$SQTY" as SHIP_QTY_OVR,

"T$GSAL" as GROSS_SALES_OVR,

"T$ACDF" as ACES_DEF_OVR,

12 as FPERIOD;

SQL Select * from

taditm900630@xxxx;

BM_Temp:

CrossTable(Measure,Value,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

FPERIOD,

AutoNumber(text(ITEM)&'|'&FYEAR&'|'&FPERIOD) as [key_field],

if (sum(NO_UNITS)<>0,sum(NO_UNITS),0) as [Shipment Qty],

if (sum(SALES_AMNT)<>0,sum(SALES_AMNT),0) as [Gross Sales]

Resident gross_profit

where ITEM > '' or Len(Trim(ITEM))>0

Group by ITEM, FYEAR, FPERIOD;

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM),

FYEAR,

FPERIOD,

AutoNumber(text(ITEM)&'|'&FYEAR&'|'&FPERIOD) as [key_field],

sum(SHIP_QTY_OVR) as [Shipment Qty],

sum(GROSS_SALES_OVR) as [Gross Sales]

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Concatenate(BM_Temp)

LOAD * Resident BM_Override_Temp;

Drop Table BM_Override_Temp;

Left Join(BM_Temp)

load [key_field],

sum(if(Value_Ovr<>0,Value_Ovr,Value)) as SHIP_QTY

Resident BM_Temp

where Measure = 'Shipment Qty'

Group by key_field;

Re: Field Not Found

buzzy996 — Tue, 14 Apr 2015 15:23:26 GMT

i think,all tables information are loading back to one table,

after completion of ur first BM_Temp data load u have to use this table as the resident in another table ,but in this case with out completion of data load (ur frist table BM_Temp ) ur trying to use that table key_field from "BM_Temp " in the same table(i mean here ur last left join assume as same table)

Re: Field Not Found

Tue, 14 Apr 2015 17:58:06 GMT

Is there a way that I can take information from the BM_Temp created and create another table from that information????

I'm trying to do something like this but I'm not getting any results with the SHIP_QTY field

BM_SHIP_QTY:

load AutoNumber(text(ITEM)&'|'&FYEAR&'|'&FPERIOD) as [key_field],

text(ITEM),

FYEAR,

FPERIOD,

sum(Value_Ovr) as SHIP_QTY_OVERRIDE,

sum(Value) as SHIP_QTY

Resident BM_Temp

where Measure = 'Shipment Qty'

Group by ITEM, FYEAR, FPERIOD;

Re: Field Not Found

Tue, 14 Apr 2015 22:30:08 GMT

First of do the below so that it will be auto concatenated.

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

FPERIOD,

AutoNumber(text(ITEM)&'|'&FYEAR&'|'&FPERIOD) as [key_field],

sum(SHIP_QTY_OVR) as [Shipment Qty],

sum(GROSS_SALES_OVR) as [Gross Sales]

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Secondly Value_Ovr,Value and Measure dont exist in the BMT_Temp table. Just fix those and I hope it should be good.