topic Re: Moving AGGR Calculation from front-end to backend in App Development

Moving AGGR Calculation from front-end to backend

Aryasmank — Mon, 21 Nov 2022 13:00:40 GMT

Hi All,

I want to move my AGGR calculation to backend from from-end.

Expression : aggr(count({<status={'Valid'}, ROD={"$(Date(max(ROD)))"}>} distinct id_pd), Date)

there are two tables :

table1: columns --> id, ROD, id_pd

table2: columns --> id, Date, status

how this expression can be done in load script .

--Mayank

Re: Moving AGGR Calculation from front-end to backend

starke_be-terna — Mon, 21 Nov 2022 13:19:59 GMT

Hi Mayank!

You need to first join both sources in your script, before you can aggregate data using "group by".

AGGR_prep: load * from table1; // LOAD table1 from source left join(AGGR_prep) // Might need other join() operation depending on use-case load * from table2; // join table2 from source // generate aggregated data AGGR_table: load Date, count(distinct id_pd) as id_pd_count resident AGGR_prep group by Date ; // get rid of helper table drop table AGGR_prep;

Be aware of the following:
By generating your count in the Qlik script you can only use the data granularity you used in the generation process (in our example Date).
Let's say you add the dimension "Department" to one of your tables. The current script I provided would not discriminate between different Departments, so when the end user makes a Department selection, the numbers would not change. This needs to be kept in mind for future changes of your application.

Best regards,
Benjamin

Re: Moving AGGR Calculation from front-end to backend

Aryasmank — Tue, 22 Nov 2022 05:42:56 GMT

Hi Benjamin,

Thank you so much , I really helpful.

--Mayank