Skip to main content

Dollar-sign expansion using a variable

ON THIS PAGE

Dollar-sign expansion using a variable

When using a variable for text replacement in the script or in an expression, the following syntax is used:

$(variablename)      

$(variablename) expands to the value in the variable. If variablename does not exist, the expansion will result in an empty string.

For numeric variable expansions, the following syntax is used:

$(#variablename)      

It always yields a valid decimal-point representation of the numeric value of the variable, possibly with exponential notation (for very large/small numbers). If variablename does not exist or does not contain a numeric value, it will be expanded to 0 instead.

Example:  

SET DecimalSep=',';

LET X = 7/2;

The dollar-sign expansion $(X) will expand to 3,5 while $(#X) will expand to 3.5.

Example:  

Set Mypath=C:\MyDocs\Files\;

...

LOAD * from $(MyPath)abc.csv;

Data will be loaded from C:\MyDocs\Files\abc.csv.

Example:  

Set CurrentYear=1992;

...

SQL SELECT * FROM table1 WHERE Year=$(CurrentYear);

Rows with Year=1992 will be selected.

Example:  

Set vConcatenate = ; For each vFile in FileList('.\*.txt') Data: $(vConcatenate) LOAD * FROM [$(vFile)]; Set vConcatenate = Concatenate ; Next vFile

In this example, all .txt files in the directory are loaded using the Concatenate prefix. This may be required if the fields differ slightly, in which case auto-concatenation does not work. The vConcatenate variable is initially set to an empty string, as the Concatenate prefix cannot be used on the first load. If the directory contains three files named file1.txt, file2.txt and file3.txt, the LOAD statement will during the three iterations expand to:

LOAD * FROM[.\file1.txt];

Concatenate LOAD * FROM[.\file2.txt];

Concatenate LOAD * FROM[.\file3.txt];

Expanding variables in alternate states

The variable has only one value, and this is used in all alternate states. When you expand a variable the value is also the same, independent of where it is made, and the state of the object.

If the variable is a calculated variable, that is, the definition starts with an equals sign, the calculation is made in the default state, unless you specify an alternate state in the variable definition.

Example:  

If you have a state named MyState, and a variable named vMyVar:

vMyvar: =only({MyState} MyField)

The variable definition content, with an explicit reference to the alternate state name, determines in which state the variable content will be evaluated.