topic Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS in QlikView

LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

vikasmahajan — Thu, 25 Sep 2014 11:02:13 GMT

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

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

ashfaq_haseeb — Thu, 25 Sep 2014 11:06:41 GMT

Hi,

Create composite key with autonumber function

regards

ASHFAQ

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

rubenmarin — Thu, 25 Sep 2014 11:24:18 GMT

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.

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

vikasmahajan — Thu, 25 Sep 2014 11:45:53 GMT

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

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

vikasmahajan — Thu, 25 Sep 2014 11:46:45 GMT

dept and bseg will be different columns.

Vikas

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

rubenmarin — Thu, 25 Sep 2014 12:00:09 GMT

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';

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

vikasmahajan — Thu, 25 Sep 2014 12:18:13 GMT

Got it thanks