sum from different fields

[xtrimf]

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 ? 🙂

[xtrimf]

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

and easy maintenance too?

[#3]

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

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

What exactly is your requirement?

[johnw]

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.

[xtrimf]

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...

[johnw · Accepted solution]

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.