Saturday, January 12, 2013

Entire Load Statement in a Variable

QlikView document variables contain text. The software allows us to use variables in different situations and, for such a simple idea, they add considerable flexibility to help you satisfy requirements.

Most people use variables for field names, label text, expressions, and other relatively short bits of text. You can store longer text in a variable that may be useful. For example, I recently stored this entire bit of loadscript code in a variable named var_UOM_table:

/* Unit of Measure - loadscript code */
if NoOfRows('TBL_UOM_SELECT')>0 then
    Drop table TBL_UOM_SELECT;
end if
Add Load * inline
[Unit of Measure: uom_conv
Box: 1
Case: 0.1
Std Drum: (1/DRUMFCTR)
] (delimiter is ':');

In the loadscript I simply wrote the single line

and that expanded to the entire segment of loadscript code when the document was reloaded.

This was useful to me for a document that loaded variable definitions from a database. Remember that variables can contain text expressions and accept arguments. Keep variables like this in mind for loadscript situations that require standard bits of text that may need to be repeated or shared between documents.

★ ★ ★

1 comment:

Steve Dark said...

I've found this approach useful in the past also. One time particularly so, as I needed to build the load script based on available fields and then also call the script multiple times in a loop. I was even able to do a replace from a base variable with placeholders in to pick up which iteration of the loop it was on.

Something like:

let vThisLoad = replace(vBaseLoad, '#IterNo', iColNo);

It's just a case of thinking out of the box and seeing what QlikView can do for you.