5 Replies Latest reply: Jul 26, 2016 7:08 AM by Marcus Sommer RSS

    Load Statement with calculated fields

    Nihal Cakir

      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

          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 ramasamy

            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.

            • Re: Load Statement with calculated fields
              Vineeth Pujari

              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
                Mindaugas Bacius
                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
                  Marcus Sommer

                  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