topic load/save calculated variable to QVD in QlikView

load/save calculated variable to QVD

Tue, 13 Mar 2012 14:33:14 GMT

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.

Re: load/save calculated variable to QVD

jagan — Tue, 13 Mar 2012 14:43:55 GMT

Hi,

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

Regards,

Jagan.

load/save calculated variable to QVD

Tue, 13 Mar 2012 15:08:24 GMT

jagan, no!

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

load/save calculated variable to QVD

sebastiandperei — Tue, 13 Mar 2012 15:11:52 GMT

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:

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

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.

Re: load/save calculated variable to QVD

jagan — Tue, 13 Mar 2012 15:12:55 GMT

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.

load/save calculated variable to QVD

Tue, 13 Mar 2012 15:29:10 GMT

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,')

Re: load/save calculated variable to QVD

Tue, 13 Mar 2012 15:32:10 GMT

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

load/save calculated variable to QVD

sebastiandperei — Tue, 13 Mar 2012 16:07:40 GMT

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...

Re: load/save calculated variable to QVD

sebastiandperei — Tue, 13 Mar 2012 17:33:39 GMT

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.

Re: load/save calculated variable to QVD

Wed, 14 Mar 2012 07:49:04 GMT

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='');

Re: load/save calculated variable to QVD

sebastiandperei — Wed, 14 Mar 2012 12:51:36 GMT

The structure is:

If vxM_Merchants_ant <>'' then

Let vxM_Merchants_ant=vxM_Merchants_ant (why want to do this??);

Else

Let vxM_Merchants_ant='';

End If;

But, this conditional has 2 contradictions:

- If V<>'' then V=V?????

- if V='' THEN V="" (the second chance of IF will occur when the condition is false, or the opposite of the condicion is true, like this case)

Sorry for my bad engrlish, but i cant understand what you needs... Try to explain better please.