topic Speeding up aggr in QlikView

Speeding up aggr

markp201 — Sun, 01 Jan 2017 13:17:33 GMT

A kpi with a complex aggr (averaging 10,000,000 detail rows over customer, region).

The customer and sales date are in dimension tables and the sales amount is in a central fact table.

I've thought about creating an autonumber for the customer and sales date.

Would aggr run faster if it was using numerical dimensions rather than string?

What impact do large tables have on aggr? If the customer table was 50 fields and the fact table had 100 measures.

If the aggr was using in a calculation, would variables help instead of having complex expressions?

vinieme12 — Sun, 01 Jan 2017 14:07:47 GMT

where are you using AGGR() , straight table or Textbox?

have you considered aggregating the measure during load, instead of front end?

markp201 — Sun, 01 Jan 2017 15:12:35 GMT

textbox (kpi).

I see - something like this?

AggrTable:

SELECT autonumber(customer&region,'aggrkey') as aggrkey,sum(salesamount)

RESIDENT

facts

Add this to the fact

autonumber(customer&region,'aggrkey') as aggrkey

MK_QSL — Sun, 01 Jan 2017 17:03:32 GMT

AggrTable:

SELECT autonumber(customer&region,'aggrkey') as aggrkey,sum(salesamount)

RESIDENT facts

Group By customer, region;

autonumber will help you to reduce the space of your application.

markp201 — Sun, 01 Jan 2017 21:03:50 GMT

In a dimensional mode, the customer and region are in their respective tables. I think you would need a join clause for the group by, correct?