Loading variable into a table

[guya]

Hi all,

I'm looking for an example of how to load variable into a table and also the synthax to peek them.

Thanks in advance,

Guy

[wizardo]

Hi Guy,

I am not sure exactly what you mean when you say "load a variable into a table" so i assume the simple case.

in QV 9 and above there is a new api method called"DynamicUpdateCommand"

with this method inside a macro it is possible to interact with the data inside a QV table (after the script is finished - e.g in the layout)

this is done via 3 flavors of the method. delete, update, insert

the syntax is a pseudo sql query that you pass to the method

here are the examples from the APIGUIDE.QVW

Rem Dynamic Data Update

sub Update
SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE * SET Discount = if(Discount >= 35, 0, if (City='Stockholm', Discount + 5, Discount + 2)) WHERE Country = 'SE'")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

sub Insert
SET Result = ActiveDocument.DynamicUpdateCommand ("INSERT INTO * (Country, City) VALUES (DK, Copenhagen), (NO, Oslo)")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

sub Delete
SET Result = ActiveDocument.DynamicUpdateCommand ("DELETE FROM CITY WHERE IsNull (Discount)")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

as for the peek function

this is a script function. it can be used only in the script.

what it does is to enable you to peek into previously loaded values inside a field

here is the syntax and some samples from the qlikview help

peek( fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the input table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Examples:

peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous( Sales ) ).

peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current input table.

peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current input table.

peek( 'Sales', 0, Tab1 )
returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.

just in case i missed your intention entirely :

i know sometimes people new to qlikview mistakenly call fields by the name variable

if this is what you meant then maybe you can try giving more details as to what you wish to achieve

Mansyno

[disqr_rm]

See example. This will load all variables into one table. It uses a marco (at On Open) to read all variables and concatenate them into one variable. This one concatenated variable then is read by for loop and put fields into one table with autogenerate. Just one idea of how it can be done. Iam sure there are many other smarter ways. Let's hear them form others..

[guya]

Thx for your reply.

I think I do not describe cleary my problem. In fact Binary Load does not bring with it all of the variables.

I got in my script somme variables definition like:

LET vEndDate = peek('EndDate',0, 'RegMinMaxDates') ;
LET vStartDate = peek('StartDate',0, 'RegMinMaxDates') ;
LET vThisYear = year($(vToday));
LET vLastYear = year($(vToday)) - 1;

and I would to store them in the source document and then retrieve them from the table (using peek()) in the target document.

With many thanks,

Guy

[#5]

We had the same problem at one time-- we wanted to load a .qvw file into another one using the Binary command in the loadscript but we wanted to get the variables also. So, we build a special table as the last step in the loadscript that simply stores the variable values in a one-row QlikView table. Later when the .qvw file is loaded as a Binary the table can be converted back into document variables.

In the loadscript we load variables into a table something like this:

VARIABLES:
Load * inline [v1_var1 ,v2_var2 ,v3_var3

$(var1), $(var2), $(var3)];

and then in the second document's loadscript, after the Binary load of the first .qvw file we rebuild the document variables something like this:

Let var1=('v1_var1',0,'VARIABLES');

Let var2=('v2_var2',0,'VARIABLES');

Let var3=('v3_var3',0,'VARIABLES');

Drop table VARIABLES;