Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello fellow Qlikview nerds,
As we all know, Qlikview does not have clearly defined "types". One little-known feature is that strings can be treated as comma delimited arrays and looped through using "for each..." as follows:
set array = '1,2,1,4,9';
for each arrayItem in $(array)
let i = $(arrayItem); // "1" > "2" > "1" > "4" > "9"
next
This is really great idea (thanks Qlikview!) but in order to take full advantage of it you need to do some nasty string manipulation and even then you're limited.
However, Qlikview macros (access with Ctrl+M from Qlikview dashboard) allow you pass arguments from the Load Script to functions written in Javascript (once "Jscript" is selected in lower-left part of macro window) which has great array manipulation built into the language.
So could we write a Javascript macro that provides us access to array methods to use on Load Script comma-delimited strings?
After a few tedious hours, I give you Javascript Array Methods in the Qlikview Load Script!
Enter the below function into your macro screen with "Jscript" selected and voila, now you can use this jsArray function:
function jsArray(array, jsFunction, argumentString){
if(array.length===0){array = [];} //handles if empty string is passed in
} else {
//forcing string conversion here handles Qlikview Load Script's lack of types
array = (""+array).split(',');
}
if(array.hasOwnProperty(jsFunction)) {
return array[jsFunction];
} else {
array.runFunction = [][jsFunction]; //find function on Array.prototype
if(argumentString || argumentString === ""){
argumentString = ("" + argumentString).split(",");
var arrayTemp = array.join(',');
var altArray = array.runFunction.apply(array, argumentString);
if(arrayTemp === array.join(',')) {
return altArray.toString();
}
} else {
array.runFunction()
}
return array.toString();
}
}
The above function, jsArray, works like so:
jsArray(array, jsFunction, argumentString)
array: the comma-delimited "array" string you are passing in from the load script
jsFunction: a string identifying the Javascript array function you want to use ('push', 'sort', 'reverse', etc.)
argumentString(optional): a string containing the arguments you want to pass to the Javascript function
identified by jsFunction
e.g.
jsArray('1,2,3', 'push', '5,4') ----> "1,2,3,5,4"
Load script examples:
set array = '1,2,hey,6,blue,3';
set argumentsExample = '1,4,imAdded,meToo';
let blah = jsArray('$(array)', '2'); // "hey"
let blah = jsArray('$(array)', 'length'); // 6
let blah = jsArray('$(array)', 'length', ''); // 6
let blah = jsArray('$(array)', 'splice', '$(argumentsExample)'); // "1,imAdded,meToo,3"
let blah = jsArray('$(array)', 'shift'); // "2,hey,6,blue,3"
let blah = jsArray('$(array)', 'unshift', '5'); // "3,1,1,2,hey,6,blue,3"
let blah = jsArray('$(array)', 'slice', '1,3'); // "2, hey"
let blah = jsArray('$(array)', 'pop'); // "1,2,hey,6,blue"
let blah = jsArray('$(array)', 'sort'); // "1,2,3,6,blue,hey"
let blah = jsArray('$(array)', 'reverse', ''); // "3,blue,6,hey,2,1"
//for each does not allow string array values
for each y in $(blah)
let i = $(y); // i = "3" > "" > "6" > "" > "2" > "1"
next
//no worries, if you need strings, just do this!
for y = 0 to jsArray('$(blah)', 'length') - 1 // subtract one as array indexes begin at zero, length does not
let i = jsArray('$(blah)', $(y)); // i = "3" > "blue" > "6" > "hey" > "2" > "1"
next
Remember that spaces after commas will be included in your array values, which could throw off some functions such as 'sort' .
If you'd like to "chain" methods you can do this:
let blah = jsArray(jsArray('$(array)', 'sort'), 'reverse', '');
Enjoy!
Best,
SirRodge
P.S. If you'd like to add Array methods you can just do so in your macros screen by adding to methods to the Array prototype.
Here's an example method "rid" you can add to the Array prototype in your macro screen that lets you get rid of specific values you don't want. For example, getting rid of empty array values, jsArray('1,,2,3','rid','') // '1,2,3'
Array.prototype.rid = function(deleteValue) {
for (var i = 0; i < this.length; i++) {
if (this == deleteValue) {
this.splice(i, 1);
i--;
}
}
return this;
};