How to use aggregation functions (e.g. Count) in the load script when there are 80+ columns

[su1]

Hello everyone, 

I have a Qlik Sense app and it's working well but I want to move my calculations (Master Measures) to the load script. 

I need to count distinct IDs of Calls and Surveys for my calculations (group by Employee IDs). To use Count(), I need to use "Group By" but it is not feasible when I have 80+ columns.

I would like to know if there are any other ways to calculate what I want. 

Thank you, everyone!

[Oleg_Troyansky]

Hi there,

May I ask you - what's the reason for moving the count calculations to the script?

Usually we advise against pre-calculating totals and subtotals in the script - it creates results that can't be sensitive to user selections, and it's a pain to associate them to the appropriate levels of details properly.

If you choose to do it anyway - then perhaps you could load a separate smaller table with just the Employee ID and the desired counts, that would be associated to the main table (with 80 fields) based on the Employee ID. However, since those are aggregated numbers, you will have to ensure that you avoid duplication when you use them in visualizations.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[su1]

Hi, 

Thank you for your answer. I want to move them because the sheet takes a bit long to load and I am thinking it might be because of the calculations. Because I have read it somewhere that making calculation at the front-end will make it slower for visualizations to load. 

Just like you said, they are insensitive to user selections and I have tried separate table method but it doesn't work well with the filters because the numbers are constant. (for example, I can't just choose the calls count for a specific week or a month)

That's why I want to know if there is another way to make these calculations. 

[elakkians]

Hi @su1 ,

Try if you can do the Count in a separate table taking Resident from the main table and associating with the main table. You can take only the required columns so that the script will be fast.

[Oleg_Troyansky · Accepted solution]

Hi there,

A few tips on performance tuning:

- Unless your data contains tens of millions of rows, calculating counts on the fly should not cause a perfromance problem

- Calculating distinct counts in charts with dimensions can be slower than usual. For that, the recommendation is to add a "counter" field with the value 1 for each unique instance of the field that you want to count. For example, if you are counting a distinct number of Calls, then add a field CallCounter in the table, where Calls are unique. You may have to generate a table like this in your load script. Then, instead of

count(distinct CallID)

you can use

sum(CallCounter),

and that will be a lot faster.

- Measures can get slow if you use heavy functions like IF() or AGGR(), try to avoid those.

- To diagnose performance issues, I recommend downloading and trying QSDA Pro. You will be able to see much clearer, what objects and what measures take too much time.

You can learn a lot more performance tuning tips at my Performance Optimization session at the Masters Summit for Qlik - coming to Orlando and to Dublin this fall!

[su1]

Thank you. I will try the counter column method you have mentioned. 

[su1]

I tried doing that before. But the numbers don't reflect what it should be when different filters are chosen.