topic Re: Load Statement with calculated fields in QlikView

Load Statement with calculated fields

nigel987 — Tue, 26 Jul 2016 08:44:29 GMT

Hi,

just a general question. I'm looking for a best practice. Is it better to seperate the Load Statement and calculated fields in terms of performance? For example, should I rather write:

Table:

Load Field A,

Field B,

Sum(Field B) AS Sales

From ...

or is it better to seperate this into:

Table:

Load Field A,

Field B

From ...

Temp*

Load *,

Sum(Field B) AS Sales

Resident Table;

Drop Table;

Thanks for any reply

Re: Load Statement with calculated fields

robert_mika — Tue, 26 Jul 2016 09:43:05 GMT

when using aggr function (SUM,MAX,COUNT..) you will need to use Group By a statement as well.

Load

Field A,

Field B,

Sum(Field B) AS Sales

From ...

group by Field A,Field B

Then you will need to map this back to your final table.

so is better to load once a whole table and then perform your calculations.

Re: Load Statement with calculated fields

MayilVahanan — Tue, 26 Jul 2016 09:46:56 GMT

You don't want to split the tables.

Table:

Load Field A,

Sum(Field B) AS Sales

From your TableName

Group by Field A;

Hope, you don't want Field B.

Re: Load Statement with calculated fields

vinieme12 — Tue, 26 Jul 2016 09:51:27 GMT

When Using Calculations in your table load, imagine working with a pivot table!

You will need to include only those fields for which you want your measure to be aggregated by.

Re: Load Statement with calculated fields

MindaugasBacius — Tue, 26 Jul 2016 10:04:35 GMT

The Pivot or Straight tables are more dynamic and flexible than defining all your measures in the script.
Also, the applications respond time is perfect when expressions in front end are used with care. While loading time will increase when doing aggregations in script.

Re: Load Statement with calculated fields

marcus_sommer — Tue, 26 Jul 2016 11:08:32 GMT

I think there couldn't be a general answer - it will always depend on the concrete scenario if an aggregation on the script-level is more suitable then doing this within the gui and if a script-solution is needed how the real requirements are.

For example, by a small dataset you don't need to worry much about performance but already by mid-sized datasets it will make a difference if you need to include all fields with an anggregartion and/or if there needs to filter something with a where-clause - this meant the combination of where- and group by statements within a single load could be significant slower then separating them.

Therefore it will be depend ...

- Marcus