load/save calculated variable to QVD

[#1]

Hi!

Currently reciving a file daily and I need to calculate some stuff from it and then save that to a QVD.

Expressions are loaded from excel and used in pivot.

When I do this

load
'=$(vM_Merchants_ant)' as meracants
AutoGenerate 1;

I save the expression, I need to save the result.

[jagan]

Hi,

Please find the attached file for solution.  Hope it helps you.

Regards,

Jagan.

[#3]

jagan, no!

I can save the expression, or in your example a number. The question is how to save a result from an expression.

[sebastiandperei]

Hi.

Anyway, depends on the way you have defined the variable. For example, If you defined like this:

1) Let vVar = Sum(Sales)

vVar will take the result of the expression in script, and it will never changes their value.

In case you define like this:

2) Set vVar = '=Sum(Sales)'

vVar will get the text of the expression ('=' included), and it will recalculate the value in every selection.

In both cases, the solution is different:

1)

load
'$(vM_Merchants_ant)' as meracants
AutoGenerate 1;

2)

Depends on the content you have assigned to the var.

Please, specify the script lines you have used to assign the data to the variable, and what kind of data will get the involved fields.

[jagan]

Hi,

Instead of

SET vValue = 100;

You can use your expression, and save that variable value in the QVD file.

Hope the updated file helps you.

Regards,

Jagan.

[#6]

This is a copy from the excel that I use to load variables,

vM_Merchants_ant

num(Sum({$< Year=,  MonthNum=,Quarter=,[Org.nummer] = {"=len([Org.nummer]) >  0"},[Moder/Dotter]-={'M'},numAvslutsdatum={0} >  }bCounter),'# ##0,')

[#7]

Try this in a load script. I want you to save the result of the expression (530) in a QVD

LET vVar='=sum({<region={1}>}sales)';
sales:
LOAD * Inline [
region,sales
1,200
1,330
2,300];
LOAD
          $(vVar) AS Value
AUTOGENERATE 1;

Rgds

thomas

[sebastiandperei]

This kind of expressions are only available for tables or graphics.

In script, the dinamic variables (those that have an expression, not a value) never take their value to the script.

You must make a action, that "Stablish another Variable" with the value of the expression. This variable will be static, and their value would be usable in the script.

I will send your example...

[sebastiandperei]

In attached you get 2 ways to do this.

The first, is using your expression variable. The action is triggered when the reload ends. Then, takes the value of vVar and put it in vV variable. In next reload, it will get this value (in table 1).

You must to know that the script area will never recognize the set analisis, only static variables.

Other way is shown in script. Obviously, with my example is easier than your variable, but if you must do the calculation in reload time, it's the only way to do it, doing the relationships and aggregations (group by, where, if, etc....)

Please, let me know if this helps you.

[#10]

Hi,

I get stuck between a not defined variable and the fact that when reloading I set the variable to '' again.

Is it possible to declair a variable with a condition?

If I don't create the new variable to hold the value it don't get set. But if I do, then I put it back to '' when reloading.

So I'd like to create it with a condition (pseudo for if not '' then as is else create as ''

let if(vxM_Merchants_ant<>'',vxM_Merchants_ant=vxM_Merchants_ant,vxM_Merchants_ant='');