topic Re: Dynamic calculation in table possible? in QlikView

Dynamic calculation in table possible?

Thu, 31 Oct 2013 07:24:26 GMT

Dear All,

Could you please tell me whether a dynamic calculation is possible?

Imagine I have a table like this (in script editor):

Table1:

Load * INLINE [

Var1, Var2, Var3,Var4,Var5

1,5,6,5,7

2,6,8,1,9

10,5,8,2,9

];

Table2:

LOAD (Var1 + Var2 +Var3) as [sum all var]

Resident Table1;

This simple situation becomes complex when

Table1 has 500 columns from Var1 up to Var500
Table1 has different column names, so Var1 = Thickness, Var2 = Spreadability
The sum of Var1..,.Var500 varies depending on the data structure and user selection
and as a consequence not all columns will be concerned in computation e.g. (Var1
+ Var2) as [sum all var], (Var12 + Var13 + Var273 + Var303) as [sum all var] etc. a LOT of possibilities.

As you may see, this is an example of dynamic environment. I suppose that this can be solved with a
macro program but I’m not very sure.

Can you please demonstrate it how to solve this?

Thanks and best regards,

Cornelis

Re: Dynamic calculation in table possible?

Thu, 31 Oct 2013 08:32:12 GMT

Instead of variables, it would be perhaps better to handle these as values of dimension.

var1, ..., var500 could become values of a dimension that you will sum up by selecting them.

Fabrice

Re: Dynamic calculation in table possible?

Thu, 31 Oct 2013 08:51:24 GMT

Thanks for your answer.

Do you have an example to demonstrate this, using the example above?

Regards,

Cornelis

Re: Dynamic calculation in table possible?

Gysbert_Wassenaar — Thu, 31 Oct 2013 09:33:57 GMT

Table1:
  Load rowno() as row, * INLINE [
  Var1, Var2, Var3,Var4,Var5
  1,5,6,5,7
  2,6,8,1,9
  10,5,8,2,9
  ];
   
Temp:
CrossTable(Var,Value)
load  * Resident Table1; 

join(Table1)
load row, sum(Value) as [sum all var]
Resident Temp
group by row;

drop table Temp;
drop field row;

Re: Dynamic calculation in table possible?

Thu, 31 Oct 2013 11:29:24 GMT

Yes, that is the answer where I'm looking for!

The crosstable option is the best to handle columns in a dynamic environment.

Best regards,

Cornelis