One thing it doesn’t do though (for obvious reasons) is to take into account the various different public holidays in different regions. The NetworkDays function is a useful function that takes two parameters and then returns the number of Monday-Fridays between them. Hopefully now you have a good idea how these variables work, so we can move on to a more advanced example. Similar variables could be used to replace null values with ‘Missing’ or with null. The variable is then used in the script like this: LOAD These can be replaced out with an IF statement, and if you have many in one file a variable can be employed: set vSetNull = if($1 = '', null(), '$1') You can see how this can become quite powerful?Īlso with text files (and sometimes poorly defined databases) instead of nulls empty strings can be returned, or even the text NULL. Additional parameters can be passed in using a comma separated list, subsequent parameters are referenced by $2 and $3 etc. The $1 can be repeated, allowing the same text to be inserted multiple times. You will note that the parameter into the variable is placed where the $1 appears in the variable code. Set vFmtDate = Date(Date#($1, '$(vSrcDateFmt)'), '$(vDateFormat)') Īnd then can be applied to a number of fields like this: LOAD The code to do this can be placed into a variable like this: let vSrcDateFmt = 'YYYY-MM-DD' When loading from a text file this needs to be done twice for each date – once to convert it from a string to a number and then again to format it as a date. Variables can help here.įor example, a common requirement is to format dates. There are some tasks that you can end up doing many times over in a load script, some of these can make code look untidy and mean that a subtle change in requirement can require a large number of changes to the load script. However, the usage of similar variables in the load script is another way they can be utilised, to clean up script and remove repetition. These variables can make code reuse even more flexible, which is great for pivot style tables where there is a fixed number of columns (perhaps 12 months) and more than one total column (perhaps a Total and an Average).Īs I suspect this usage of parameterized variables has been covered elsewhere before I will not repeat it here. The ability to pass parameters into variables is something I have been using for some time, after picking up the technique from Stephen Redmond’s blog.
In this post I show how and give a recipe for a NetworkDays function that excludes public holidays.
The implementation of variables and dollar sign expansion in QlikView and Qlik Sense is incredibly flexible and can be used to great effect in load scripts.
0 kommentar(er)
