6 Replies Latest reply: Sep 25, 2014 8:18 AM by vikas mahajan RSS

    LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

    vikas mahajan

      Dear all

       

      I have following tables 

      AccLoc_CodeBusSeg_CodeDimension Set IDDoc_NumDoc_Date
      46618PIJNGO0900016/30/2009 0:00

      AND

      Dimension Set IDDimCodeDimNameDim
      46618100FormulationBSEG
      46618200MUMBAIALOC

       

      I Want to apply left join  with   Accloc_code = ALOC and Busseg_Code = BSEG , I want to update in  table a

       

      How to apply left join for getting desired o/p. Following is my script  but this gives error  general script failuer

      VLE:

      LOAD   [Item No_] AS Item_Code

         ,0  AS [Lot No_]

         ,[Posting Date]

            ,[Item Ledger Entry Type] AS Entry_Type

            ,[Document No_] AS Doc_Num

            ,[Location Code] AS Location_Code

            ,[Inventory Posting Group] AS Inventory_Post_Grp_Code

        // ,0 AS Dept_Code

      //      ,0 AS Div_Code

         ,[Source No_] AS ValueEntryKey

            ,[Source Posting Group]

            ,[Item Ledger Entry No_] AS Item_Ledger_Entry_No

            ,[Valued Quantity]

            ,[Item Ledger Entry Quantity]

            ,0 AS [Manufacturing Date]

            ,0 AS [Expiration Date]

            ,0 AS [Unit of Measure Code]

            ,[Invoiced Quantity] AS Quantity

            ,[Cost per Unit] AS Cost_Per_Unit

            ,[Sales Amount (Actual)] AS Sales_Amt_Actual

            ,[Sales Amount (Expected)] AS Sales_Amt_Expected

            ,[Global Dimension 1 Code] AS BusSeg_Code

            ,[Global Dimension 2 Code] AS AccLoc_Code

            ,[Source Type]

         ,[Cost Amount (Expected)] AS Cost_Amt_Expected

            ,[Cost Amount (Actual)] AS Cost_Amt_Actual

            ,[Reason Code] AS Reason_Code

            ,[Gen_ Bus_ Posting Group] AS Gen_Bus_Post_Grp_Code

            ,[Gen_ Prod_ Posting Group] AS Gen_Prod_Post_Grp_Code

            ,[Document Date] AS Doc_Date

            ,[External Document No_]

            ,[Document Type] AS Doc_Type

            ,[Capacity Ledger Entry No_]

            ,0 AS RemainingQuantity

            ,[Valuation Date] AS ValuationDate

            ,0 AS [Expected Quantity]

            ,0 AS [Supp_Lot_No]

            ,[Dimension Set ID]   ;

      LEFT JOIN

      LOAD

      DimSetID as [Dimension Set ID],

      DimCode as Dept_Code

      FROM D:\Production\Working-Vikas\NAVQVDS\DIM_QV_DIMENSIONS.qvd (qvd)

      Where Exists([Dimension Set ID]);

        

       

       

      SELECT        [Item No_]

         ,0 AS [Lot No_]

         ,[Posting Date]

            ,[Item Ledger Entry Type]

            ,[Document No_]

            ,[Location Code]

            ,[Inventory Posting Group]

        //  ,0 AS Dept_Code

      //      ,0 AS Div_Code

         ,[Source No_]

            ,[Source Posting Group]

            ,[Item Ledger Entry No_]

            ,[Valued Quantity]

            ,[Item Ledger Entry Quantity]

            ,0 AS [Manufacturing Date]

            ,0 AS [Expiration Date]

            ,0 AS [Unit of Measure Code]

            ,[Invoiced Quantity]

            ,[Cost per Unit]

            ,[Sales Amount (Actual)]  

            ,[Sales Amount (Expected)]

            ,[Global Dimension 1 Code]

            ,[Global Dimension 2 Code]

            ,[Source Type]

         ,[Cost Amount (Expected)]

            ,[Cost Amount (Actual)]

            ,[Reason Code]

            ,[Gen_ Bus_ Posting Group]  

            ,[Gen_ Prod_ Posting Group]

            ,[Document Date]

            ,[External Document No_]

            ,[Document Type]

            ,[Capacity Ledger Entry No_]

            ,0 AS RemainingQuantity

            ,[Valuation Date]

            ,0 AS [Expected Quantity]

            ,0 AS [Supp_Lot_No]

            ,[Dimension Set ID]

      FROM [INDOCONAV\INDNAV7].[INDOCO_2013R2_Deployment].[dbo].[Indoco Remedies Ltd$Value Entry] ;

       

      Thanks

       

      Vikas