Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
...
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;
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]
I see the error. I thought I had that in place but I guess I overlooked it. That fixed the problem.