Load static value for variable in script

[mazacini]

Hi

I have a table with a ItemVal field, which is a numerical field.

I want to create a static value for the total value of the ItemVal field.

I thought this would work, but it doesn't.

Let vItemValTotal = sum(ItemVal);

Is this possible?

I know I can enclose in single apostrophe and use it with $ expansion, but I don't want to do that.

Thanks

[Gysbert_Wassenaar · Accepted solution]

Like this:

Temp:

LOAD sum(ItemVal) as SumOfItemVal FROM ...MyTable...;

LET vItemValTotal = peek('SumOfItemVal');

Drop Table Temp;

[mazacini]

Thanks Gysbert.

Would you mind if I ask you some questions?

So my LET statement in isolation does not actually perform the SUM calculation?

Does the LET statement in your example have to follow immediately on from the LOAD?

I've only ever used PEEK in the context of adding a field to a table based on data already in that table.

But from your solution, it operates across the model as a whole also? I assume it is significant that the "SumOfItemVal' field contains only a single value?

Kind Regards

Joe

[mazacini]

BTW, I should point out that your solution worked perfectly!

[Gysbert_Wassenaar]

So my LET statement in isolation does not actually perform the SUM calculation?

That's correct. The Sum can only be calculated in a load statement.

Does the LET statement in your example have to follow immediately on from the LOAD?

No, anywhere after the Temp table with the sum is created and before that table is dropped.

But from your solution, it operates across the model as a whole also? I assume it is significant that the "SumOfItemVal' field contains only a single value?

Without passig an additional parameter peek will use -1 to retrieve a value from the last row of the source table. If the field exists in more then one table then you need to specify the name of the source table too.

[mazacini]

That's great, Gysbert.

Thank you for your help.

Joe