We had a good discussion in Chapter 4, Advanced Expressions, on how to use variables with the Dollar-sign Expansion. Variables are so important to what we do in the script that it is worth just briefly reviewing the topic from a script point of view.
Variables can be assigned in the script using either a Set or Let statement.
A Set statement will assign the text on the right-hand side of the statement to the variable. A Let statement will try and evaluate the text on the right-hand side as an expression and will assign the result of that evaluation (which might be null!) to the variable. For example:
Set v1=1+1;
This will result in the v1 variable that contains the value 1+1. Consider the following example:
Let v2=1+1;
This will result in the v2 variable that contains the value 2.
A variable can be used simply in assignment to other variables. For example:
Let v3=v2+1;
The v3 variable will have the value 3 (2+1). Let's consider another example:
Let v4=v1+1;
This will not work! That is because v1 contains a string value, so a string plus a number does not make sense. However, we can do this:
Let v4=v1&'+1';
Now, v4 will have the value 1+1+1.
Generally, we use variables by using the Dollar-sign Expansion. In this case, the variables are wrapped in parentheses and preceded by the dollar sign. There is a two-step execution where the contents of the variable are first expanded and replace the dollar sign, and then, the expression is evaluated as if the value had been typed there in the first place. For example:
Let v5=$(v1)+1;
In the first step, the value of v1, that is 1+1, will be expanded and will replace the dollar sign:
Let v5=1+1+1;
In the second step, the expression is evaluated and the value of 3 is assigned to v5. We have seen previously that we can watch this two-step process in action using the central panel in the debug window:
We need to be careful with this because there might be unintended consequences. For example:
Let vToday=Today(); Let vYesterday=$(vToday)-1;
We might wonder why the vYesterday variable has a value of -0.9998411122145! This makes sense if we think that the value of vToday is something like 8/25/2014 (August 25, 2014), so the second assignment will actually end up being:
Let vYesterday=8/25/2014-1;
A better way to assign this is:
Let vYesterday='$(vToday)'-1;
Note
Note that if you are using dates like this, it is much better to assign the numeric value of the date (for example, 41876—the number of days since December 30, 1899) rather than the text representation because we have to always be sure that the text value will parse correctly whereas the numeric value is already parsed. Floor is a useful function for this as it also removes any time portion.
The following approach is better:
Let vToday=Floor(Today()); Let vYesterday=$(vToday)-1;
If a variable is assigned a null value (either from a failed expression or using the Null() function), then the variable will be removed (or not created!). This is useful to tidy up variables at the end of the script.
There are some common use cases of using variables that come up in many applications, so it is worth examining them here.
It is very useful to know which day of the week it is. There is a simple function, Today(), that will return the date for today. However, does it really give today's date? It depends! The function can take a parameter:
|
Parameter value |
Description |
|---|---|
|
| |
|
|
The date at the time when the function is called |
|
|
The date when the document was opened |
It is interesting that many of us use this function without considering that the default value is 2—the date when the document was opened. Depending on circumstances, this might not be what we want at all! This is where it can be useful to assign the result of the function to a variable:
Let vToday=Floor(Today(1));
By placing this call at the beginning of our script, we can then ensure that we are always using the same date throughout the script.
We might also be interested in a timestamp, and the Now() function will give us this. However, this function also has parameters that we need to be aware of.
|
Parameter value |
Description |
|---|---|
|
| |
|
|
The date/time at the time when the function is called |
|
|
The date/time when the document was opened |
These are slightly different from the Today() function, and the default is also different, which is 1, the time of the function call. We might need to be careful of this because if it is included in a long loop, it will be recalculated many times. It is a much better idea to calculate the value at the beginning of the script:
Let vNow=Num(Now(1));
As with the Floor() function for today, the Num() function will transform our timestamp into a numeric value. However, we might not always want the value in this format. We might want to have it in a particular format to use with database queries:
Let vCurrentExecution=Timestamp(vNow, 'YYYYMMDD hh:mm:ss'); Orders: SQL SELECT OD.* FromvwOrderDetail OD WHERE OD.TimeStamp>= '$(vPreviousExecution)' AND OD.TimeStamp< '$(vCurrentExecution)'; Let vPreviousExecution=vCurrentExecution;
So, here, I use the TimeStamp() function to assign a format to the timestamp value. I can then use this in a SQL query.
In this example, we also have a second variable, which we fill with the current timestamp upon script completion. When the document is saved, we should expect that this variable should be saved with it so that on the next reload, the query should just get the delta change in the orders table. However, what about the very first execution? How can we populate this value if it hasn't been populated before?
A part of the problem is that if a variable hasn't been populated, then it won't exist, so we can't compare it to a value. What we can do, though, is Dollar-sign expand it. If it doesn't exist, the expansion just returns an empty string. We can check the length of this string to see whether it is blank:
If Len('$(vPreviousExecution)') = 0 Then
// Set the variable to an arbitrary date in the past
Let vPreviousExecution='19990101 00:00:00';
End IfWe have discussed the uses of relative paths in loading and storing files. This is a generally good idea but there are circumstances where you might need to have absolute paths; for example, when using UNC paths for files, or if you might have changed paths for different purposes.
In these circumstances, rather than relative paths, we will specify the majority of the path using a variable. We might have this in our script:
Set vSourcePath='\QVDataServerSalesSource'; Set vQVDPath='\QVServerQVDPath';
Then, we will perform loads like this:
Sales: LOAD * FROM [$(vSourcePath)SalesReport.csv] (txt, utf8, embedded labels, delimiter is ',', msq); Store Sales into [$(vQVDPath)Sales.qvd];
If the initial assignment of variables was kept in a separate file, it could be shared amongst several files using an include Dollar-sign Expansion:
$(Must_Include=PathVariables.qvs);
We use the Must_Include syntax here because the include must succeed for the script execution to run successfully.
We have seen already that we can examine a variable value in the lower-right panel of the debug window. However, the debug window might not always be where we want it to be when executing a script, especially long running scripts. There is a better way.
The Trace statement will echo whatever is typed after it, up to its semicolon statement terminator, to both the script execution dialog and to the document log. As it is a standard statement, we can include variables with the Dollar-sign Expansion and expect their values to be echoed. For example:
Trace Previous Execution: $(vPreviousExecution); Trace Current Execution: $(vCurrentExecution);
This will result in something similar to this Script Execution Progress dialog:
If the Generate Logfile option is selected in the Document Properties, then the Trace result will also be echoed in the logfile:
Note
There is no real reason as to why we should not generate a logfile. It saves a load of time in troubleshooting reload issues, especially server executed reloads. If the option is turned on, a file called QVWName.qvw.log is created in the same folder as the QVW. If it is a server reload, the logfile is also copied into the Distribution Services log folder for that task.
It is possible to nest one or more Dollar-sign Expansions. This allows us to create some interesting functionality in scripts.
As an example, consider the variables used for path names. Imagine that we have a separate set of paths to be tested from production. We can do something like this:
Set vTestOrProd='Test'; Set vServerTest='\DataServerTestTestFiles'; Set vServerProd='\DataServer1Production'; Set vSourcePath='$(vServer$(vTestOrProd))Sources'; Set vQVDPath='$(vServer$(vTestOrProd))Sources';
We will see something like this in the Debugger window:
When we nest expansions like this, the inner expansions will be performed first. In this example, the inner expansion sets the name of the variable for the outer expansion.
This might look like a simplified test, but we can actually use something like this, using include files and windows security, to allow only certain people to update the script to start using production files!
We can make a variable calculation a little more intelligent by actually passing parameters to it. This way, it can be like a pseudofunction.
When creating variables with parameters, we can only do so with the Set statement. The Let statement doesn't make any sense here because it tries to evaluate it at the time of assignment, so we can't pass a parameter.
We create parameters by using a dollar sign with a number. We can add multiple parameters; we just need to up the numeric sequence. For example:
Set vAdd=($1+$2); Let vRes=$(vAdd(1,1)); Trace Result of add: $(vRes);
This will yield a result of 2. Not terribly complex.
As another example, how about if we wanted a function to format a 10-digit phone number in the (nnn) nnn-nnnn format. We can write a variable like this:
Set vPhone='(' &Left($1,3) & ') ' & Mid($1,4,3) & '-' & Right($1,4);
Load Phone, $(vPhone(Phone)) As Formatted Inline [
Phone
2025551234
2125554321
];This is a relatively straightforward calculation but we can have this as complex as we like. Indeed, we can have several of such variables stored in an external file and then include them.
A subroutine is used where we have, generally, a more complex requirement, and we know that we are going to have to repeat it quite often.
A great example of using a subroutine that can be used in most implementations is the repetitive task of storing a table to QVD and then dropping the table that we will have in loader applications. We might implement it like this:
Sub StoreAndDrop(vTableName) Store [$(vTableName)] into [$(vQVDPath)$(vTableName).qvd]; Drop Table [$(vTableName)]; End Sub
Then, later in the script, we will call:
Call StoreAndDrop('TableName');Note that the subroutine must be loaded in the script before it is called. We can also pass multiple parameters to the subroutine. The parameters become local variables in the subroutine. These are not available outside the subroutine, but variables defined outside the subroutine, global variables, are available.