topic Re: Using the data model/assosisations to create aggregate table in QlikView

Using the data model/assosisations to create aggregate table

Fri, 20 Sep 2013 10:07:03 GMT

Hi,

We have quite complex data model and lots of data in our Qlikview report. I'd like to use the data model/assosisations and data in this Qlikview file to create simple aggregated and precalculated view (monthly totals) as a new table and then save this data as a .qvd file so I could then load that smaller dataset to another Qlikview report.

Is this possible in Qlikview scripting, or is my only option basically to display the aggregated data as a chart table, export it to file and then read that file in the other Qlikview report?

Re: Using the data model/assosisations to create aggregate table

swuehl — Fri, 20 Sep 2013 10:08:59 GMT

No, you can aggregate in the script using GROUP BY in your LOAD and aggregation functions, like

LOAD Customer,

sum(Sales) as SummedSales

RESIDENT SalesTable GROUP BY Customer;

Re: Using the data model/assosisations to create aggregate table

Fri, 20 Sep 2013 10:18:19 GMT

But I cannot use values from other tables?

For example:

MonthlyTotalsByProduct:

LOAD

MasterCalendar.Month,

Product.Name,

COUNT(Customer.Id) AS TotalCustomers,

SUM(Sales.Sum) AS TotalSales

GROUP BY

MasterCalendar.Month,

Product.Name

;

Re: Using the data model/assosisations to create aggregate table

swuehl — Fri, 20 Sep 2013 10:23:00 GMT

Not sure if I understand correctly, you want to reference fields from different tables in your aggregated table aggregation functions?

You need to create a table with your data to aggregate first (using e.g. CONCATENATE or JOIN), then LOAD this table with a GORUP BY clause. The associative logic works only in the frontend.

Re: Using the data model/assosisations to create aggregate table

Fri, 20 Sep 2013 10:32:34 GMT

Yes, I wanted to use the associative logic in my script, but it looks like that isn't possible.