Thursday, February 12, 2009

Capture Variable Values in the Loadscript


I often put statements in the loadscript that record current conditions as document variables in case the information might be useful someday when checking into a problem or simply to document the origin of the data. Here’s a few that have proved useful:

let reload_time = now();
let user = OSuser();
let computer = ComputerName();
If IsPartialReload() then
   let partial_reload_time = now();
Else
   let partial_reload_time = '';
End If

Those variables can be displayed on the title tab or in a corner of the main tab.

You can capture information from the data that is being loaded and store it in a variable. This is useful for control information that would be displayed on the title page like the system information above. It can also be useful to control If statements and Loops and Where clauses in the loadscript.

Here’s an example that reads a date from an Oracle table and stores it in a QlikView document variable so it can be displayed in a chart title:

//Find OHPOST date and store in a one-row table
DUNK_OHPOST:
SELECT TO_CHAR(MAX(OHPOST),'MM/DD/YYYY') AS DUNK_OHPOST FROM SKU;

//Now, put the value into a document variable
Let DUNK_OHPOST=Date(Peek('DUNK_OHPOST',0,'DUNK_OHPOST'));
Drop table DUNK_OHPOST;

That Peek function takes 3 arguments: fieldname, row number, and table name. For a one-row table use zero to indicate the first row. If that is a unique field name then you could also use the FieldValue function to retrieve the value from the first field value.

In older QlikView versions you could use the Peek function in a text object but that isn't allowed any longer. So, store the values in document variables and you can display them in text object or chart titles. Consider displaying data values that tell the report user something important about the data; such as earliest or latest posting dates, high or low order numbers, current fiscal period, currency conversion factor, source application version number, security or classification warning, etc.

2 comments:

Robson CandĂȘo said...

Please put a example of the script? i tryied to put in my script and get error.

-TB said...

A small QlikView document that demonstrates the technique is available here-- http://finmagic.0catch.com/. Sorry about the advertisements on that website - just click on the download link for the document and see how it works for you.