topic sum from different fields in QlikView

sum from different fields

xtrimf — Tue, 29 Sep 2009 21:46:43 GMT

Hi,

Is it possible to write a dynamic expression in a pivot / straight table without "if" clause?

for example, an expression that sums a field according to row number :

row1, sum(Field1)

row2, sum(Field2) etc...\

something along "sum(Field&rowno())" that will work ? 🙂

sum from different fields

prieper — Tue, 29 Sep 2009 21:59:32 GMT

What would be the purpose / intension to do so? The usual way would be to have Field1, Field2 etc as Dimension and then Sum(xxx) as expression.

Peter

sum from different fields

xtrimf — Tue, 29 Sep 2009 23:11:11 GMT

It just gives me the freedom to use dynamic field names in a calculation,

it is very useful when for each dimension row you'd like to use a different calculation.. especially for P&L reports.

sum from different fields

Tue, 29 Sep 2009 23:22:20 GMT

sum your values in your load script and display it in a straight table.

sum from different fields

prieper — Tue, 29 Sep 2009 23:23:08 GMT

But then I think it might make even more sense to assign certain fields to a variable and to build then your pivot or other charts on basis this variable?

Peter

sum from different fields

xtrimf — Tue, 29 Sep 2009 23:30:28 GMT

it would still force me to use "IF" clause

sum from different fields

xtrimf — Tue, 29 Sep 2009 23:35:19 GMT

"Sum your values in your load script and display it in a straight table."

where's the fun in that?

sum from different fields

johnw — Tue, 29 Sep 2009 23:43:09 GMT


xtrim wrote:it would still force me to use "IF" clause

I guess I'm confused. What's wrong with using an IF clause? Are you worried about performance? In this case, the IF is outside of the summation, so it should be very efficient:

if(rowno()=1,sum(Field1)
,if(rowno()=2,sum(Field2)
,if(rowno()=3,sum(Field3))))

Not that it will perform any differently than an IF, but I'd probably use pick() instead for brevity:

pick(rowno(),sum(Field1),sum(Field2),sum(Field3))

Performance should only be a problem if you embed the pick() in the sum, so I don't recommend this:

sum(pick(rowno(),Field1,Field2,Field3))

But even there, it's possible that QlikView is smart enough to realize that rowno() doesn't have to be calculated every time, and will perform just fine.

sum from different fields

Tue, 29 Sep 2009 23:44:17 GMT

The fun is in getting the job done fast and easy.

sum from different fields

xtrimf — Tue, 29 Sep 2009 23:49:32 GMT

Hi John,

I have about a hundred rows in each report...that is why i'm trying to avoid the if.

sum from different fields

xtrimf — Tue, 29 Sep 2009 23:51:04 GMT

"The fun is in getting the job done fast and easy. "

and easy maintenance too?

sum from different fields

Wed, 30 Sep 2009 00:03:20 GMT


xtrim wrote:
"The fun is in getting the job done fast and easy. "

and easy maintenance too? <div></div>

What exactly is your requirement?

sum from different fields

johnw — Wed, 30 Sep 2009 00:24:21 GMT

OK, so then EVERY one of the 100 expressions is a simple sum, just of a different field? And your fields really ARE named Field1, Field2 and so on? If so, that sounds very strange, and I might understand your question, but I don't think I understand your requirement. And I don't think I have an answer for your question, as I'm not sure there's any way to do something like sum(Field&rowno()). I could be wrong. But there also might be a way to meet your real requirement without having 100 fields with "nonsense" names and 100 separate sum statements. So like dragonauta, I'd like more information on your actual requirement.

sum from different fields

xtrimf — Wed, 30 Sep 2009 03:40:41 GMT

The situation is that I need to build a report between to non-linked tables.

One table holds the report categories, and the other the Data.

It is not possible to link between them for various reasons.

For each category (Dimention) I need a different calculation (not just sum(Field1)).

as was previously suggested, In my load script i calculated the complex things and already insert each calc in different field,

so in the end all I would have to do is sum(FieldX), but still the "IF' is bugging me....

maybe its the only solution, I don't know...just trying.

strange that a simple thing like dynamic field name is not built-in...

sum from different fields

johnw — Wed, 30 Sep 2009 04:13:29 GMT

If for each category you "need a different calculation (not just sum(Field1))", then I am again failing to see what is wrong with either an if() or a pick(). You MUST specify every calculation SOMEWHERE. So there's no way around writing 100 calculations. The overhead of a pick() is negligible if you can use rowno().

pick(rowno()
,expression 1
,expression 2
,expression 3
...
,expression 100)

However, depending on how everything is laid out, you might be able to handle it in the script. I'm guessing you might be able to have a table with ID/expression/result fields:

ID expression result
12345 total sales 5000
12345 avarage monthly sales 300
12345 total cost 4000

And then your chart would have "expression" as a dimension. You'd probably set up the table with a crosstable load:

CROSSTABLE (expression,result)
LOAD
ID
,expression 1 as "total sales"
,expression 2 as "average monthly sales"
,expression 3 as "total cost"
...
,expression 100 as "whatever"
FROM YourTable
;

Now again, you're still having to maintain all 100 expressions, as there's no way around that. But you might get a performance benefit from doing it during the load rather than at execution time. Execution time might be more flexible, however, since it can for instance refer to fields from multiple tables.

I do think a weakness of QlikView is the ability to refer to different meanings of fields, in this case the inability (?) to assemble the field name with string commands and then just use it like a field.