Calculated Fields

[#1]

Trying to set up some calculated fields during my database pull-in.

This is what I've put it in   SUM([Field A]*[Field B)])/SUM([Field B]) as FieldC,

However, it causes a number of errors with recognising my SQL query, as well as my master calendar script and I can understand why. Any tips would be greatly appreciated on the correcting method of inputting calculated fields.

[sinanozdemir]

Hi Sam,

Where are you putting this expression, in the load script or in the front end?

If it is in the load script, make sure that you are also using GROUP BY clause.

[#3]

In the load script, I want to do this for several fields and the create a list box with all the calculated fields. How would I apply the GROUP BY clause to the statement to make it work?

[maxgro · Accepted solution]

better if you post the script and the error you get, not just a line

in your line you have an aggregation function, sum

so usually you need a group by with all the fields not in aggregation functions, something like

load

     a,

     b,

     c,

     sum(a*b) as ...,

     sum(...),

     min(....)

from

     ....

group by

     a,b,c;

[sinanozdemir]

As maxgro suggested. Although, it would be better if you could post a sample dataset:

LOAD

     Field A,

     Field B,

     Field D,

     Sum(Field A * Field B)/Sum(Field B)

FROM/Resident...

GROUP BY

     Field D

ORDER BY (This is optional)

     Field D

[sasiparupudi1]

if you are doing a resident load in qlikview, then the syntax would be slightly different to the SQL syntax.

base table from SQL Source

table1:

SQL load

   [Field A],

    [Field B)]

     [Field D],

[Field E]

From some_sql_table;

noconcatenate

summarytable:

load

    SUM([Field A]*[Field B)])/SUM([Field B]) as [Field C],

     [Field D],

[Field E]

resident table1

group by

[Field D],

[Field E];

You may drop the main table if you do not need it after creating the summary table

drop table table1;

hth

Sasi