Re: SQL script in Qlikview - Qlik Community

mohan2391 · ‎2016-09-23

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

tresesco · ‎2016-09-23

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

sushil353 · ‎2016-09-23

Hi,

Are you using the same database/connection string?

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

HTH

Sushil

mohan2391 · ‎2016-09-23

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 ?

tresesco · ‎2016-09-23

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

sharuta · ‎2016-09-23

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