Skip to content

Instantly share code, notes, and snippets.

Avatar
🏔️
Bringing DevOps to Qlik Sense - one tool at a time. Hello SenseOps!

Göran Sander mountaindude

🏔️
Bringing DevOps to Qlik Sense - one tool at a time. Hello SenseOps!
View GitHub Profile
@mountaindude
mountaindude / wblGen.bat
Created Oct 16, 2015 — forked from nate-untiedt/wblGen.bat
A simple batch file that automatically generates the wblfolder.wbl for your Qlik Sense visualization extension.
View wblGen.bat
@echo off
REM wblGen.bat - v 1.0.0 - 2015-10-09
REM Description:
REM A simple batch file that automatically generates the wblfolder.wbl for your Qlik Sense visualization extension.
REM
REM Author: Nate Untiedt - Analytics8 - nuntiedt@analytics8.com
REM
REM Credit to: http://stackoverflow.com/a/8387078
setlocal EnableDelayedExpansion
@mountaindude
mountaindude / sense_find_max_value_of_field
Created Oct 11, 2015
Fastest way of finding max/min values of a field in Sense or QV
View sense_find_max_value_of_field
// The obvious way of finding the max/min value of a field might something like this:
//
// LOAD max(salesDate) Resident dataTable;
//
// However, if dataTable is very large (hundred of millions of lines or more, with many columns), the above will be
// very slow, as we will end up traversing that entire table.
//
// Instead, the fastest method of achieving the desired result is to load the FieldValue()s.
// We then only look at the values of the salesDate field itself, and not the entire dataTable table.
// Performance gains by many orders of magnitude can be gained by using this method.
@mountaindude
mountaindude / sense_us_state_region_division.txt
Created Oct 7, 2015
QlikView / Sense code mapping US states to regions and divisions
View sense_us_state_region_division.txt
// Load US high level regions (easter, southern, midwest etc) and divisions
// From https://en.wikipedia.org/wiki/List_of_regions_of_the_United_States
// Could also use https://www.npac.com/the-npac/about/npac-regions if more granularity is needed
US_States:
NoConcatenate Load
[State code],
[US census region],
[US census division]
Inline [
State code,US census region,US census division
@mountaindude
mountaindude / sense_set_sum_per_max_date
Last active Oct 24, 2017
QlikView / Sense set expression to pick the data for the last available date
View sense_set_sum_per_max_date
// Sum FieldName per the last available date we have data for, withinin the current selection
Sum( {$<DateNum={$(#=Max(DateNum))}>} FieldName)
@mountaindude
mountaindude / sense_set_general
Last active Oct 11, 2015
General set analysis syntax
View sense_set_general
// General syntax for set analysis
{$<Product = Product + {OurProduct1} – {OurProduct2} >}
@mountaindude
mountaindude / sense_create_qvd_load_table
Last active Mar 26, 2020
QlikView / Qlik Sense code to create table with all QVDs to load later in a script
View sense_create_qvd_load_table
// List all QVDs that should be loaded
// In this example the QVDs have a filename format of YYYYMMDD.qvd
//
// The rationale for loading QVDs this way, rather than using something like
// load "/some/path/*.qvd" where date > SomeDate
// is that the latter will have to test the date field of all lines within all QVDs in that directory.
// That might take a long time (and it will become slower over time, if new QVDs are added every day), whereas the method
// used in the code below offers (very close to) constant loading time.
//
For each Filename in 'D:\path\to\qvds'
@mountaindude
mountaindude / sense_set_max_date
Last active Oct 24, 2017
QlikView / Qlik Sense set expression to find largest date
View sense_set_max_date
// Use in chart title/subtitle/footer for describing what data is shown in a chart, etc
='Chart contains data up until and including ' & Date(Max({1} Date), 'YYYY-MM-DD')
@mountaindude
mountaindude / date_variables.qvs
Last active Jan 23, 2018
QlikView and Qlik Sense date variables
View date_variables.qvs
let vStartOf2ndPreviousQuarter = Date(Floor(QuarterStart(Today(), -2)), 'YYYY-MM-DD');
let vStartOf2ndPreviousQuarterNum = Num(Date(Floor(QuarterStart(Today(), -2)), 'YYYY-MM-DD'));
let vStartOfPreviousQuarter = Date(Floor(QuarterStart(Today(), -1)), 'YYYY-MM-DD');
let vStartOfPreviousQuarterNum = Num(Date(Floor(QuarterStart(Today(), -1)), 'YYYY-MM-DD'));
let vStartOfCurrentQuarter = Date(Floor(QuarterStart(Today())), 'YYYY-MM-DD');
let vStartOfCurrentQuarterNum = Num(Date(Floor(QuarterStart(Today())), 'YYYY-MM-DD'));
let vStartOfPreviousMonth = Date(Floor(MonthStart(Today(), -1)), 'YYYY-MM-DD');