Dynamic calculation in table possible?

[#1]

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

[#2]

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

[#3]

Thanks for your answer.

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

Regards,

Cornelis

[Gysbert_Wassenaar · Accepted solution]

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;

[#5]

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