topic Re: Load static value for variable in script in QlikView

Load static value for variable in script

mazacini — Wed, 28 Oct 2015 18:38:51 GMT

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

Re: Load static value for variable in script

Gysbert_Wassenaar — Wed, 28 Oct 2015 18:41:33 GMT

Like this:

Temp:

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

LET vItemValTotal = peek('SumOfItemVal');

Drop Table Temp;

Re: Load static value for variable in script

mazacini — Wed, 28 Oct 2015 18:58:12 GMT

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

Re: Load static value for variable in script

mazacini — Wed, 28 Oct 2015 19:05:44 GMT

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

Re: Load static value for variable in script

Gysbert_Wassenaar — Wed, 28 Oct 2015 19:06:39 GMT

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.

Re: Load static value for variable in script

mazacini — Wed, 28 Oct 2015 19:09:39 GMT

That's great, Gysbert.

Thank you for your help.

Joe