Moving AGGR Calculation from front-end to backend

[Aryasmank]

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

[starke_be-terna · Accepted solution]

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

[Aryasmank]

Hi Benjamin, 

Thank you so much , I really helpful.

--Mayank