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

You can specify which state to use when expanding a variable. Changes in a specific state do not affect variable values that are expanded in another state. If you do not specify a state, the variable is expanded in the default state.

Example:  

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

  • $(vMyVar) expands the variable in the default state.
  • $({MyState} vMyVar) expands the variable in the MyState state.