Have you ever wanted to load dollar sign expansion $() into a field or variable? One common use case is storing expressions within a metadata table, which may contain a variable in the expression. Have you needed to leverage a square bracket [ ] in an Inline Load? Maybe have had to use a quote ‘ in a variable or field value. Here is how you can get around that.
Dollar Sign Expansion
When you have dollar sign expansion in the load script, the first thing that will happen is the formula will be evaluated. This is typically used to have a variable name replaced with its definition. Another common example on the front end is pre-evaluate an equation, so that the result of that equation will be used to determine the final equation.
Let’s say we have a series of variables that contain our set analysis expression for time periods: Day, WTD, MTD, QTD, and YTD. On the front end we have an chart that is dynamic. The user can choose how they want to see the data. In most cases this includes a filter with the options: Day, WTD, MTD, QTD, YTD.
To throw in some additional complexity, we can add the ability to have the measure be dynamic. This allows the user to choose not only what time period they are seeing, but which measure.
One option to solve the use case is create a meta data table which contains the different expressions. These expressions would also need to leverage dollar sign expansion to determine which Set Analysis variable to use, so that the right time frame is shown.
Since the interpreter tries to expand the dollar sign expansion when it gets to it, we will need to create the syntax in multiple steps. The easiest way is to put a placehold for the dollar sign
$ and replace it later, so that it happens within the data layer instead of the script layer.
Set Analysis Variables
Here we can avoid the dollar sign expansion by concatenating the $ to the (, so the result is the syntax of dollar sign expansion, versus in the string itself.
For our example, we have three expressions we want to leverage: Quantity, Gross Sales, and Net Sales.
- Gross Sales
- Net Sales:
To set up the mechanic to make things dynamic lets create a series of metadata tables.
period – This will define the timeframe the user wants to see.
measure – This will contain the measure details.
Notice how we have put a placeholder (
???) for the dollar in the dollar sign expansion, which we later replace with a
Sum(???(vSet???(=Only(period))) qty) will result in
To leverage what we did in the script, we can create a measure as such:
Example 01 – Day Gross Sales
Example 02 – MTD Net Sales
Example 03 – YTD Quantity
This is a great example of leveraging dollar sign expansion and Qlik’s order of operations to create advanced UI/UX mechanics.
Now our fields were pretty simple. What if we had a field that had a space in the calculation?
[ would cause an error in our Inline Load. Luckily what we can do is use double quotes
" to wrap our inline data. This allows the start identifier
[ to be used in the data.
If we need to use both square brackets and double quotes, we can leverage a similar process as our dollar sign expansion expample and replace a place holder.
Lastly, what if you want to incorporate single quotes
' in a variable? Luckily there are a couple of ways.
First and the most common example is to concatenate the quote in using
chr(39) which is the character code for a single quote.
Escaping the Quote
You can escape a single quote by putting another single quote in front of it. This tells the interpreter that the single quote is a part of the string and not a start/termination of one.
Set command doesn’t evalue the text between the equal sign and the semi-colon. So you can add a quote without any additional accomodation. The one limitation is you cannot evaluate an expression using
To wrap up, there are several ways you can get around incorporating the text you need, whether that is in a field or variable. The most important thing is to think about how Qlik’s engine is evaluating different items, and seeing if you can order the process to meet your needs or if there is another available syntax which doesn’t conflict with your use case.