Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

SQL script in Qlikview

Hi,

I got the following script from 1 of the existed qvw.

But when I run the same script in my Qlikview desktop, it is throwing error msgs as TRIM, CASE not existed functions.

My question is how they are using this file and why I can't ?

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

$(Include=..\..\..\Include\WW\QuoteTAT\vertica.qvs);

Details:

Load *;

SQL

WITH PL_Mapp AS (select distinct PROD_MLTGRN_CD, PROD_LN_ID, GBL_BUS_UNIT_DN, BUS_GRP_DN from egig_App_Sales_Pipeline_A.PROD_MLTGRN_D_V)

 

SELECT      

        TRIM(fsqd.Sales_Quote_Id) as "Quote ID",

        TRIM(fsqd.Product_Line_Id) as "Product Line",   

       floor( SUM(fsqd.Sales_Quote_Detail_Standard_Price_USD_Amount ) )as "Standard price USD",

  floor(SUM(fsqd.Sales_Quote_Detail_Authorized_Price_USD_Amount)) as "Auth Price USD",

  floor(SUM(fsqd.Sales_Quote_Detail_Margin_USD_Amount)) as "Margin USD",    

        CASE (fsqd.Sales_Quote_Source_System_Name)

                WHEN 'ICC' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'DirectPlus' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'Manual Input' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'DealReg' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'SFDC' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                ELSE COALESCE(NULLIF(TRIM(pmdv.GBL_BUS_UNIT_DN),''), 'Not Mapped')

        END as "GBU",      

        TRIM(fsqd.Sales_Quote_Deal_Size_Name) as "Deal Size bucket",

        CASE WHEN fsqd.Sales_Quote_Source_System_Name NOT IN ('Eclipse','Manual Input')

  THEN CASE WHEN EXISTS

  (SELECT Sales_Quote_Id FROM egig_App_Quote_Effectiveness.DIM_Sales_Quote WHERE Sales_Quote_Id=fsqd.Sales_Quote_Id

  AND Sales_Quote_Include_Indicator='Y') THEN 'Y'  ELSE 'N' END

  ELSE TRIM(fsqd.Sales_Quote_Include_Indicator) END as "Include Flag"       

FROM egig_App_Quote_Effectiveness.Fact_Sales_Quote_Detail fsqd 

        left join PL_Mapp pmdv ON fsqd.Product_Line_Id=pmdv.PROD_MLTGRN_CD

  WHERE   EXISTS (SELECT * FROM egig_App_Quote_Effectiveness.Fact_Sales_Quote WHERE Sales_Quote_Id=fsqd.Sales_Quote_Id

  AND Sales_Quote_Approved_Timestamp >= trunc(add_months(SYSDATE,-13),'MM') )

  GROUP BY "Quote ID","GBU","Deal Size Bucket",pmdv.BUS_GRP_DN,"Include Flag","Product Line";

5 Replies
MVP
MVP

Perhaps you are not using the same database. And the fact could be their db supports those functions and yours doesn't.

Master II
Master II

Hi,

Are you using the same database/connection string?

also try to run the sql script using some SQL Developer first.

HTH

Sushil

Creator II
Creator II

Hi Amit & Sushil

Thanks for quick reply.

Yes, I'm not using their database.

So, as per you if i get access to that database and then if i reload the qvw, it won't throw me error right ?

they also included a file on top with include statement, so do i need to collect that file also ?

MVP
MVP

Yes, the included file is a script file. You should get that file too and place on your system at the proper path.

Contributor II
Contributor II

Hi.

Try IT

Create first on SQL DB view with your script

Then: select  * from "your_view_name" in QVw

In this case Time requery  - minimum