Discussion Board for collaboration on QlikView Scripting.
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:
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
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
WHERE rn = 1
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.
Did you tried without count in order by clause? Try once, i think this will help you to reduce cost.
ORDER BY QM_QUOTE_NUM DESC) rn