topic Re: aggr count distinct optimization in QlikView

aggr count distinct optimization

lprasanth — Sat, 16 Nov 2024 01:22:14 GMT

I want the following code to be optimized. can anyone please help me on this

(count(if(WildMatch(ID,'GR*')>0,
aggr(count({<idStatut={14}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,Gln_Name_Dpt,ID),
aggr(count({<idStatut={14}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,ID)))
-
count(if(WildMatch(ID,'GR*')>0,
aggr(count({<idStatut={12}, idResp={'CARRIER'}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,Gln_Name_Dpt,ID),
aggr(count({<idStatut={12}, idResp={'CARRIER'}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,ID))))
/
count(if(WildMatch(ID,'GR*')>0,
aggr(count({<idStatut={14}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,Gln_Name_Dpt,ID),
aggr(count({<idStatut={14}>}DISTINCT ID),Wse_Name,[Channel Name],MoisePrévu,Corpo_Name,ID)))

Re: aggr count distinct optimization

sunny_talwar — Mon, 03 Feb 2020 13:17:19 GMT

It would be easier to respond to this if we have a sample where we can test out a solution.... Without a sample the only thing you can look into changing is to move the if statement (WildMatch(ID,'GR*')) to the backend to create a flag in the script.... the flag can be used in set analysis...

Re: aggr count distinct optimization

Brett_Bleess — Wed, 26 Feb 2020 17:08:27 GMT

In addition to what Sunny posted, I can only add the following Design Blog posts:

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

https://community.qlik.com/t5/Qlik-Design-Blog/When-should-the-Aggr-function-NOT-be-used/ba-p/1467855

There are likely some other posts that could be helpful too, so here is the base URL for the area you can use to search further on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett