Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nigel987
Contributor II

Load Statement with calculated fields

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

Tags (1)
1 Solution

Accepted Solutions

Re: Load Statement with calculated fields

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

5 Replies

Re: Load Statement with calculated fields

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

Hi

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.

vinieme12
Esteemed Contributor II

Re: Load Statement with calculated fields

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.

MindaugasBacius
Valued Contributor II

Re: Load Statement with calculated fields

  1. The Pivot or Straight tables are more dynamic and flexible than defining all your measures in the script.
  2. 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

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

Community Browser