topic Re: Calculated Fields in QlikView

Calculated Fields

Fri, 14 Aug 2015 13:34:06 GMT

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.

Re: Calculated Fields

sinanozdemir — Fri, 14 Aug 2015 13:36:53 GMT

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.

Re: Calculated Fields

Fri, 14 Aug 2015 13:38:30 GMT

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?

Re: Calculated Fields

maxgro — Fri, 14 Aug 2015 13:38:39 GMT

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

sum(a*b) as ...,

sum(...),

min(....)

from

....

group by

a,b,c;

Re: Calculated Fields

sinanozdemir — Fri, 14 Aug 2015 13:42:50 GMT

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

Re: Calculated Fields

sasiparupudi1 — Fri, 14 Aug 2015 14:09:40 GMT

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