LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

[vikasmahajan]

Dear all

I have following tables 

AccLoc_Code	BusSeg_Code	Dimension Set ID	Doc_Num	Doc_Date
		46618	PIJNGO090001	6/30/2009 0:00

AND

Dimension Set ID	DimCode	DimName	Dim
46618	100	Formulation	BSEG
46618	200	MUMBAI	ALOC

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

[ashfaq_haseeb]

Hi,

Create composite key with autonumber function

regards

ASHFAQ

[rubenmarin]

Hi Vikas, not sure of what you need, in the sample you provided, If a record with "Dimension Set ID=46618 (and Accloc_code = ALOC and Busseg_Code = BSEG)" exists it will duplicate the record, one with Dept_Code=100 and the other with Dept_Code=200.

Is that what you want?

Probably I don't understand it right.

[vikasmahajan]

Thanks

Please note I have re-created issue and attached excel sheet with detailed requirement

Plase give solution

create table with left join along with multiple dimension

Vikas

[vikasmahajan]

dept and bseg will be different columns.

Vikas

[rubenmarin · Accepted solution]

I'm guessing... you want to populate each dimension in his column?

If it's you can first load VLE (not the Codes values) and populate the value of each dimension in different steps:

VLE:

LOAD   [Item No_] AS Item_Code

//      ,[Global Dimension 1 Code] AS BusSeg_Code

//      ,[Global Dimension 2 Code] AS AccLoc_Code

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

Left Join

LOAD

DimSetID as [Dimension Set ID],

DimCode as BusSeg_Code

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

Where DIM='BSEG';

Left Join

LOAD

DimSetID as [Dimension Set ID],

DimCode as AccLoc_Code

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

Where DIM='ALOC';

[vikasmahajan]

Got it thanks