Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vikasmahajan
Honored Contributor III

LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

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

Tags (3)
1 Solution

Accepted Solutions

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

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

6 Replies

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

Hi,

Create composite key with autonumber function

regards

ASHFAQ

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

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
Honored Contributor III

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

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
Honored Contributor III

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

dept and bseg will be different columns.

Vikas

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

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
Honored Contributor III

Re: LEFT JOIN WITH QVD FOR MULTIPLE DIMENSIONS

Got it thanks

Community Browser