1 Reply Latest reply: Aug 8, 2017 3:52 PM by Sunny Talwar RSS

    Why does the script's progress shows an '-1'?

    Julio Andrés Arriaga Rangel

      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.


      Capture1.PNG

       

      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;

        • Re: Why does the script's progress shows an '-1'?
          Sunny Talwar

          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, 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);


          Concatenate (CosttoServe_Fact)

          LOAD *

          Resident Temp;


          DROP Table Temp;

           

          STORE CosttoServe_Fact into [CosttoServe_Fact.qvd] (qvd);

           

          DROP Table CosttoServe_Fact;