Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Thanks in advance for your help. I am loading qvds automatically concatenated by the same field names. One of the loads is generating a crosstable. Why does the Script execution progress shows an '-1'? I add the screen and script.
Regards.
CosttoServe_Fact:
load [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_TMA.qvd] (qvd);
CosttoServe_Fact:
load [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_Sales.qvd] (qvd);
CosttoServe_Fact:
crosstable(Item,Value,8)
load date(date#(left(start_time,10),'YYYY-MM-DD'),'YYYY-MM-DD') as [Date_Key]
,'N/A' as [Customer ID]
,'N/A' as [Product ID]
,'N/A' as [Sales Center Name]
,'N/A' as [Sales District Name]
,'N/A' as [Route ID]
,'N/A' as [Route Type]
,'Actual' as [Definition Table]
,total_cost
,planned_cost
,total_line_haul_cost
,total_stop_off_charges
,total_fuel_cost
,total_drop_charges
,total_wait_charges
,total_detention_charges
,total_ferry_charges
,total_toll_charges
,total_unloading_charges
from [CosttoServe_Extract_OTM_CB.qvd] (qvd);
store CosttoServe_Fact into [CosttoServe_Fact.qvd] (qvd);
drop table CosttoServe_Fact;
It seems like the script is creating a new table for the CrossTable load instead of auto concatenating it to the above two tables. May be create a temp table and then concatenate the resident load from Temp table
CosttoServe_Fact:
LOAD [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_TMA.qvd] (qvd);
Concatenate (CosttoServe_Fact)
LOAD [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_Sales.qvd] (qvd);
Temp:
CrossTable (Item, Value, 😎
LOAD date(date#(left(start_time,10),'YYYY-MM-DD'),'YYYY-MM-DD') as [Date_Key]
,'N/A' as [Customer ID]
,'N/A' as [Product ID]
,'N/A' as [Sales Center Name]
,'N/A' as [Sales District Name]
,'N/A' as [Route ID]
,'N/A' as [Route Type]
,'Actual' as [Definition Table]
,total_cost
,planned_cost
,total_line_haul_cost
,total_stop_off_charges
,total_fuel_cost
,total_drop_charges
,total_wait_charges
,total_detention_charges
,total_ferry_charges
,total_toll_charges
,total_unloading_charges
from [CosttoServe_Extract_OTM_CB.qvd] (qvd);
Concatenate (CosttoServe_Fact)
LOAD *
Resident Temp;
DROP Table Temp;
STORE CosttoServe_Fact into [CosttoServe_Fact.qvd] (qvd);
DROP Table CosttoServe_Fact;
It seems like the script is creating a new table for the CrossTable load instead of auto concatenating it to the above two tables. May be create a temp table and then concatenate the resident load from Temp table
CosttoServe_Fact:
LOAD [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_TMA.qvd] (qvd);
Concatenate (CosttoServe_Fact)
LOAD [Date Key]
,[Customer ID]
,[Product ID]
,[Sales Center Name]
,[Sales District Name]
,[Route ID]
,[Route Type]
,[Definition Table]
,[Item]
,[Value]
from [CosttoServe_Sales.qvd] (qvd);
Temp:
CrossTable (Item, Value, 😎
LOAD date(date#(left(start_time,10),'YYYY-MM-DD'),'YYYY-MM-DD') as [Date_Key]
,'N/A' as [Customer ID]
,'N/A' as [Product ID]
,'N/A' as [Sales Center Name]
,'N/A' as [Sales District Name]
,'N/A' as [Route ID]
,'N/A' as [Route Type]
,'Actual' as [Definition Table]
,total_cost
,planned_cost
,total_line_haul_cost
,total_stop_off_charges
,total_fuel_cost
,total_drop_charges
,total_wait_charges
,total_detention_charges
,total_ferry_charges
,total_toll_charges
,total_unloading_charges
from [CosttoServe_Extract_OTM_CB.qvd] (qvd);
Concatenate (CosttoServe_Fact)
LOAD *
Resident Temp;
DROP Table Temp;
STORE CosttoServe_Fact into [CosttoServe_Fact.qvd] (qvd);
DROP Table CosttoServe_Fact;