Load Statement with calculated fields

[nigel987]

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

[robert_mika]

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.

[MayilVahanan]

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]

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]

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.

[marcus_sommer · Accepted solution]

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