Problem with crosstable

[#1]

I have two separate files and I'm trying to create one crosstable that will contain information from both tables, however my value_over appears to have no information but I know that there are records that should be showing up.  Here is my load script; any suggestions as to why my value_over is not picking up the data????

bench_table:

LOAD FYEAR,

     FPERIOD,

     text(ITEM) as ITEM,

     NO_UNITS,

     SALES_AMNT

FROM

[..\QVD\bench.qvd]

(qvd);

bench_override:

LOAD

    text("T$ITEM") as ITEM,

    "T$SQTY" as SHIP_QTY_OVR,

    "T$GSAL" as GROSS_SALES_OVR;

SQL Select * from table_1;

BM_Temp:

CrossTable(Measure,Value,3)

LOAD

    text(ITEM) as ITEM,

    FYEAR,

    FPERIOD,

    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 bench_table

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,

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

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

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Concatenate(BM_Temp)

LOAD * Resident BM_Override_Temp;

Drop Table BM_Override_Temp; 

When I look at the table for an item it doesn't show any value for value_ovr but of course there's a value in there because SHIP_QTY_OVR is showing something.

[swuehl]

Could be just a copy & paste issue, but shouldn't it be like

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

    text(ITEM) as ITEM,

    FYEAR,

...

[#3]

not sure what you are asking.

here is my load script; basically I want the crosstable to have the item, measure, value, and value_ovr so that I know if there's an override value for that item

bench_table:

LOAD FYEAR,

     FPERIOD,

     text(ITEM) as ITEM,

     NO_UNITS,

     SALES_AMNT

FROM

[..\QVD\bench.qvd]

(qvd);

bench_override:

LOAD

    text("T$ITEM") as ITEM,

    "T$SQTY" as SHIP_QTY_OVR,

    "T$GSAL" as GROSS_SALES_OVR;

SQL Select * from table_1;

BM_Temp:

CrossTable(Measure,Value,3)

LOAD

    text(ITEM) as ITEM,

    FYEAR,

    FPERIOD,

    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 bench_table

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,

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

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

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Concatenate(BM_Temp)

LOAD * Resident BM_Override_Temp;

Drop Table BM_Override_Temp; 

[swuehl]

I'm asking myself if you should better alias your field called text(ITEM) to ITEM.

Do you have an additional field 'text(ITEM)' when looking at the table BM_Temp? I believe you want to have this field instead be concatenated to ITEM, so you need an alias here, too.

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

    text(ITEM) as ITEM,

    FYEAR,

    FPERIOD,

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

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

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

[If you use an expression like text(ITEM) in your script, QV will use the expression also as field name unless you use AS to state the alias]

[#5]

I see the error.  I thought I had that in place but I guess I overlooked it.  That fixed the problem.