Field Not Found

[#1]

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;

[buzzy996]

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)

[#3]

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;

[#4]

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.