Transforming Variables to Strings for the Load Script using Sublime Text

    Hello Qlik Community,


    Writing Qlik Expressions in Sublime Text 3

    Qlik expressions are a very powerful tool for making an app responsive. We can use expressions for dimensions, measures, labels, colors, sorting, titles, axis boundaries, calculation conditions, and even sheet names. While this kind of flexibility is great, it is sometimes difficult to keep track of where all your expressions reside. If you realize that, for example, a set expression is incomplete, you may need to search through dozens of places to correct your expressions. A great solution to this problem is to store some (or all) of your expressions as variables in a separate document, and then copy those variables to the load script. Instead of writing a full expression in the app, where it can get lost, you'll simply expand the variable [e.g., $(myVar)] in the expression editor.


    I personally love Sublime Text 3 for writing code. It's lightweight, yet extremely adaptable. In fact, there is QlikView package for Sublime Text, made by vadimtsushko, which is outlined here (Note: I use Qlik Sense, and it's fine for that too):

    QlikView language plugin for Sublime Text Editor


    Loading Variables as Strings

    Yet, there is still a problem. If you want the variable to be responsive, you want to load it as a string, and then use dollar sign expansion in the app to evaluate the variable on demand. hic's blog is very helpful on this point. For example, the following posts describe when variables will be evaluated.

    The Magic of Variables

    The Magic of Dollar Expansions

    The Little Equals Sign

    Also marcus_sommer, keeps a nice repository of techniques about variables: Variables


    So, in many cases you'll want variables in the load script that look like this:

    let myVar = 'your expression';


    But, we now run into another problem. If your expression has single quotes embedded, then it will break the string. Similarly, if your expression has dollar signs - depending on where they are - internal expressions may be expanded before you want them to (although I must admit that I'm still not 100% on when this will happen).


    In many cases, a good workaround for these problems is to load single quotes as Char(39) and dollar signs as Char(36). For example, from this:

    let maxScore = Num( Max({<Year = {"$(=$(vMaxYear))"}>} Score), '#0');


    You'll need this to load directly in your load script:

    let maxScore = 'Num( Max({<Year = {"'&chr(36)&'(='&chr(36)&'(vMaxYear))"}>} Score), '&chr(39)&'#0'&chr(39)&')';


    Sublime Text Commands: Convert Variable to String

    This is all starting to get complicated (and ugly!). Luckily, with the power of Sublime Text packages, we can create a command that will do all the formatting of your variables so that you can just copy and paste them in the load script. After loading, you'll see these variables in the Variable Editor just like you would any other variable.


    To get this working, you'll first need to install the RegReplace package in Sublime Text. Here are some instructions:

    Installation - RegReplace Documentation


    Once you get this package installed you'll have three files that need to be edited or replaced. Here is where I found each on my system.

    1.     Rules. C:\Users\YourUserName\AppData\Roaming\Sublime Text 3\Packages\User\reg_replace_rules.sublime-settings
    2.     Commands. C:\Users\YourUserName\AppData\Roaming\Sublime Text 3\Packages\User\Default.sublime-commands
    3.     Key Bindings. C:\Users\YourUserName\AppData\Roaming\Sublime Text 3\Packages\User\Default (Windows).sublime-keymap

    Here's what going on in each file:

    1. This is a JSON format file that stores an array of regular expression commands that will be enacted by your command. In the attached version, I have X rules that will be applied to turn the variable into a string. For example, the first rule, "single_quote_replace", replaces any single quotes found within an expression with '&chr(39)&', which breaks the string, appends a character, and then starts the string again. Read the RegReplace documentation to write new rules.

    2. This file stores arguments for the reg_replace command. The "replacements" array is composed of a sequence of rules stored in the (1) file. Once this is set, you can apply a command through the command palette (Ctrl-shift-p), then look for the "caption".

    3. This file stores a command, like 2, but will be enacted with a keyboard shortcut. In my version, I bind my command to Ctrl-Shift-q.


    Once you have this set up, try applying the command (or keyboard shortcut) on the attached LoadVars.qvs file. You can then copy the text into the data load editor within the attached 'Test Variables Loaded in Data Load Script.qvf' file. If the load works, you'll see numbers on the first sheet of the app.



    Just a couple warnings.

    1. Performance. Personally, I like to use these expressions everywhere where there could be an expression in my apps. This keeps all my expressions in a central location. So, for example, the expression in a Master Measure may point directly to a variable from the load script (e.g., vMM_MaxScore). Obviously, Qlik needs to do some extra work to expand the variable. I do not know if this represents a significant hit to performance. My feeling is that this sort of dollar sign expansion is a minor operation. Any performance hit should be more than compensated by the use of the same variable in multiple locations. But, I just don't know.
    2. Infinite Loops. I did something silly by accident on one of my apps. Because of some hasty find-and-replacing, I had a variable that looked like this: let varA = $(varA) + 1. It loaded alright, but during calculations it went into an infinite spiral. This not only broke the app, but the whole server (cpu at 100%). People were not pleased. So be careful when you compose variables of other variables.
    3. Old Variables. If you change the name of your loaded variables, you will notice that your old variables (prior to the name change) are still in the variable editor in the Qlik Sense app. If you have objects using these expressions they will reflect the expressions prior to any name change. You'll have to delete all of these old variables manually. It's painstaking, so make sure to give your variables good names from the start or you'll be deleting old variables forever.


    Your Help

    The rules in RegReplace file (1) were composed piecemeal as I ran into new cases of expressions that weren't being properly formatted. Undoubtedly, there are more elegant expressions and cases that are not taken care of. Please comment below if you find expressions that are not converted to text in an appropriate manner. I will update files, including the test files to demonstrate these cases. Please keep in mind that the test app is not intended to be meaningful or do things the best way, it just shows a sample of how variables can be written and loaded.


    Good Luck and Leave a Comment or Question!