topic Re: Store Variable value in QVD in QlikView

Store Variable value in QVD

Tue, 10 Jul 2012 14:05:54 GMT

Dear all,

I want to store a variable in a QVD file. Therefore I use the following script:

TableKPI:

Load

'$(Load_Date)' as Load_Date,

'$(TCAssessed)' as TCAssessed,

'$(TCPassed)' as TCPassed

AutoGenerate 1;

Concatenate(TableKPI)

Load * from TableKPI.qvd (qvd);

Store TableKPI into TableKPI.qvd (qvd) ;

I have defined the variables TCAssessed and TCPassed in my Qlikview file. They are both expressions.

TCAssessed=count(Value1)

TCPassed= sum(if(Value1= '1',1,0))

The problem is that Qlikview doesn't store the value into the qvd but the formula.

How can I store the numeric value instead of the formula?

Thanks in advance!

Kind regards,

Jens

Re: Store Variable value in QVD

jvitantonio — Tue, 10 Jul 2012 14:16:58 GMT

Use LET infront of the variable

LET TCAssessed=count(Value1)

Re: Store Variable value in QVD

Tue, 10 Jul 2012 14:26:38 GMT

Thanks for your quick reply but could you indicate where you put the LET?

At the moment I define this variable in my QV dashboard (so from Settings> Variable Overview).

I don't see where I should put the LET.

Can I define the variable directly in my Load script? Before I define my table?

Re: Store Variable value in QVD

jvitantonio — Tue, 10 Jul 2012 14:30:31 GMT

Correct. You can define it in your script.

Re: Store Variable value in QVD

Tue, 10 Jul 2012 14:37:33 GMT

When I define my attributes in my load script I get several errors when loading the data.

This is my script:

LET TCAssessed=count([# of possible tc]);

LET Load_date=Today();

Let TCPassed= sum(if([# of possible tc]='BAS',1,0));

TableKPI:

Load

$(Load_Date) as Load_Date,

$(TCAssessed) as TCAssessed,

$(TCPassed) as TCPassed

AutoGenerate 1;

Concatenate(TableKPI)

The error message reads:

Syntax error, missing/misplaced FROM:

TableKPI:

Load

as Load_Date,

as TCAssessed,

as TCPassed

AutoGenerate 1

TableKPI:

Load

as Load_Date,

as TCAssessed,

as TCPassed

AutoGenerate 1

Do you have any idea what the problem might be?

Load * from TableKPI.qvd (qvd);

Store TableKPI into TableKPI.qvd (qvd) ;

Re: Store Variable value in QVD

jagannalla — Tue, 10 Jul 2012 14:56:39 GMT

Hello,

You can't do like that. See in your variable you are writing the expression so when you start loading the script the variable will not hold any value, b'coz the variable hold the value once the load is completed. The expression in the variable will not written any value while application is loading.

If you want to test the application just give the value of the variable manually i.e TCAssessed=1 and load the application it will work.

-If you want to work it please create dummy variables and try to store static values which are written by the expression, then use those variables in the script.

Otherwise,

Temp:
Load TCAssessed,TCPassed from TableKPI.qvd (qvd);
LET vTCAssessed= FieldValueCount('TCAssessed');
Drop Table Temp;
TableKPI:
Load
'$(TCAssessed)' as TCAssessed,
AutoGenerate 1;
Concatenate(TableKPI)
Load * from TableKPI.qvd (qvd);
Store TableKPI into TableKPI.qvd (qvd) ;

Hope it helps you

cheers!

jagan

Re: Store Variable value in QVD

Thu, 12 Jul 2012 09:07:57 GMT

Thanks Jagan!

You answer helped a lot and I was able to create a workaround by using a button.

Thanks for the help!

Re: Store Variable value in QVD

jagannalla — Fri, 13 Jul 2012 09:25:50 GMT

Can you please mark as correct answer. It helps others to understand the problem easily.