Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Return Max of a Count in QVD

Hi Guys

Here is a query I tried to do in SQL but my IT Team say its too intensive on the db, and suggested if find a way in the QVD to find a solution.

I am trying to first count the quotes by Cust & Region then return the Cust and Region on the Max Number of Quotes.

Is there a way to use my QM.QVD to get the same?

Here is what I ran in SQL:

WITH recordsList

AS

(

    SELECT  QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes,

            DENSE_RANK() OVER  (PARTITION BY QM_CUST_CODE

                                ORDER BY COUNT(QM_QUOTE_NUM) DESC) rn

    FROM    QM

    WHERE   QM_QUOTE_JOB = 0 --signifies that its a quote not a job

    GROUP   BY QM_CUST_CODE, QM_XX_REGION

)

SELECT  QM_CUST_CODE, QM_XX_REGION, Quotes

FROM    recordsList

WHERE   rn = 1

Tags (2)
2 Replies
nirav_bhimani
Contributor III

Re: Return Max of a Count in QVD

Hi,

I think you need to pull all the field from the base table then in the resident table pull the few field on which you want to do group by clause, then do the order by Quotes.

Regards,

Nirav Bhimani

Not applicable

Re: Return Max of a Count in QVD

Hi

Did you tried without count in order by clause? Try once, i think this will help you to reduce cost.

WITH recordsList

AS

(

    SELECT  QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes,

            DENSE_RANK() OVER  (PARTITION BY QM_CUST_CODE

                                ORDER BY QM_QUOTE_NUM DESC) rn

    FROM    QM

    WHERE   QM_QUOTE_JOB = 0 --signifies that its a quote not a job

    GROUP   BY QM_CUST_CODE, QM_XX_REGION

)

SELECT  QM_CUST_CODE, QM_XX_REGION, Quotes

FROM    recordsList

WHERE   rn = 1

Community Browser