topic Save variable VALUE from application to text, csv, qvs, or qvd file in QlikView

Save variable VALUE from application to text, csv, qvs, or qvd file

Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

All I want to do is take a variable VALUE (not the =(whatever), the actual number value) and export it to one of the aforementioned file extensions.

Any ideas are greatly appreciated.

Thanks!

Joe

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Nicole-Smith — Wed, 01 Jun 2016 20:43:16 GMT

You can do it in the load script using the STORE function:

Store

A QVD or a CSV file can be created by a store statement in the script. The statement will create an explicitly named QVD or CSV file. The statement can only export fields from one logical table. The text values are exported to the CSV file in UTF-8 format. A delimiter can be specified, see Load. The store statement to a CSV file does not support BIFF export.

store[ *fieldlist from] table into filename [ format-spec ];

*fieldlist::= ( * | field ) { , field } ) is a list of the fields to be selected. Using * as field list indicates all fields.

field::= fieldname [as aliasname ]

fieldname is a text that is identical to a field name in the table. (Note that the field name must be enclosed by straight double Quotation Marks in Scripting or square brackets if it contains e.g. spaces.)

aliasname is an alternate name for the field to be used in the resulting QVD or CSV file.

table is a script labeled, already loaded table to be used as source for data.

filename is the name of the target file. The interpretation of file name is similar to names in load statements, i.e. the directory statements apply.

format-spec ::= ( ( txt | qvd ) )

The format specification consists of a the text txt for text files, or the text qvd for qvd files. If the format specification is omitted, qvd is assumed.

Examples:

Store mytable into xyz.qvd (qvd);

Store * from mytable into xyz.qvd;

Store Name, RegNo from mytable into xyz.qvd;

Store Name as a, RegNo as b from mytable into xyz.qvd;

store mytable into myfile.txt (txt);

store * from mytable into myfile.txt (txt);

(The two first examples have identical function.)

It would look something like this:

SET vVariable = 'Hello world';

Table:
LOAD '$(vVariable)' as [Variable Value]
AutoGenerate 1;

STORE [Variable Value] FROM Table INTO YourFile.txt (txt);

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Nicole-Smith — Wed, 01 Jun 2016 20:45:18 GMT

Additionally, you could create a text object with the variable in it, and someone could right click and "Export to Excel":

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Wed, 01 Jun 2016 20:55:35 GMT

This is what I tried in the beginning and it gives me the whole equation... not the value. I'll just give you the example as I should have in the beginning. I created a variable on the front end using the "Variable Overview" and multiple tables from the data model:

Variable: vRentention

Value:

=num((sum({$<SNAPSHOT_MONTH_ID = {'>$(=addmonths(max(SNAPSHOT_MONTH_ID),-12))<=$(=max(SNAPSHOT_MONTH_ID))'}>}RENEWFLAG) +

sum({$<SNAPSHOT_MONTH_ID = {'>$(=addmonths(max(SNAPSHOT_MONTH_ID),-12))<=$(=max(SNAPSHOT_MONTH_ID))'}>}REJOINFLAG))

/sum({$<SNAPSHOT_MONTH_ID = {'>$(=addmonths(max(SNAPSHOT_MONTH_ID),-12))<=$(=max(SNAPSHOT_MONTH_ID))'}>}ELIGIBLEFLAG ),'##.##%')

I need the VALUE of that, not the equation to be saved.

Thank you for your answers, I really appreciate it!

Joe

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Nicole-Smith — Wed, 01 Jun 2016 21:15:57 GMT

You're only able to store static values from variables, so you won't be able to do it with your calculation. You would need to use the text object/Export to Excel option in order to use the calculation.

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Wed, 01 Jun 2016 21:16:49 GMT

Is there a away to automate this export?

Re: Save variable VALUE from application to text, csv, qvs, or qvd file

Nicole-Smith — Wed, 01 Jun 2016 21:26:05 GMT

You may be able to use a macro to do it (#7 in this list):

Useful Qlikview Macros

How to execute a macro in script:

Execute a Macro inside load script

Disclaimer: I have not tested this and do not know if it will work, but it's the only way I think you could possibly do it.