how to use of CASE WHEN EXISTS with qlikview load

[vikasmahajan]

Dear all

I have following load script for  getting Navision financial data  I want to know  how

CASE WHEN EXISTS(

  SELECT (d.[Document No_])

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

  WHERE d.[Document No_] = a.[Document No_])

  THEN 'Y'

  ELSE 'N'

  END AS [IsProduct]

This will work in script it is wright syntax ?

Please help me urgent help required.

Thanks

Vikas

------------------------------ Original Script -----------------------------------------

FACT_GLE:

LOAD

      [Dimension Set ID]

       ,[Entry No_]

      ,[G_L Account No_]

  // ,[Dimension Value Code] AS Dept_Code

     // ,[Dimension Value Code] AS Div_Code

      ,[Posting Date]

      ,[Document No_]

   ,[Document Type]

      ,[Description]

      ,[Source Code]

     // --, d.[Description] AS [Source Desc] --d.[Description]

      ,[Amount]

      ,[Global Dimension 1 Code] AS BusSeg_Code

      ,[Global Dimension 2 Code] AS AccLoc_Code

      ,[Quantity]

       ,[Gen_ Posting Type]

      ,[Gen_ Bus_ Posting Group]

      ,[Gen_ Prod_ Posting Group]

      ,[Document Date]

      ,[Source No_]

      ,[Location Code]

      ,[IsProduct] ;

SQL SELECT

      a.[Dimension Set ID]

      ,a.[Entry No_]

      ,a.[G_L Account No_]

  //  ,c.[Dimension Value Code] AS Dept_Code

//      ,b.[Dimension Value Code] AS Div_Code

      ,a.[Posting Date]

      ,a.[Document No_]

   ,a.[Document Type]

      ,a.[Description]

      ,a.[Source Code]

      --, d.[Description] AS [Source Desc] --d.[Description]

      ,a.[Amount]

      ,a.[Global Dimension 1 Code]

      ,a.[Global Dimension 2 Code]

      ,a.[Quantity]

       ,a.[Gen_ Posting Type]

      ,a.[Gen_ Bus_ Posting Group]

      ,a.[Gen_ Prod_ Posting Group]

        ,a.[Document Date]

       ,a.[Source No_]

      ,a.[Location Code]

      ,CASE WHEN EXISTS(

  SELECT (d.[Document No_])

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

  WHERE d.[Document No_] = a.[Document No_])

  THEN 'Y'

  ELSE 'N'

  END AS [IsProduct]

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

//where [Document No_]='HO/EX/NGR/0910/00001';

Left Join

LOAD

DimSetID as [Dimension Set ID],

DimValCode as  Div_Code

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

Where DimCode='DIV';

Left Join

LOAD

DimSetID as [Dimension Set ID],

DimValCode as  Dept_Code

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

Where DimCode='DEPT';

[jonathandienst]

Hi

Everything in the statement starting with SQL is passed to the DB server and executed there. So if the SELECT query works in SQLMS or Toad, then it should work in Qlikview.

The preceding load is executed by Qlikview after the SQL SELECT returns from the DB server and so that needs to be in QV syntax.

HTH

Jonathan

[vikasmahajan]

thanks

[jonathandienst]

I think the order of execution will be:

1. The SQL select
2. The preceding load
3. The left join

[vikasmahajan]

Please Can you explain with my Original Script  .

Vikas