Skip to content

Instantly share code, notes, and snippets.

Avatar

Ralf Becher ralfbecher

View GitHub Profile
@ralfbecher
ralfbecher / QlikView_Concat_Aggregation_Limitation.qvs
Created Feb 16, 2014
QlikView textbox object and chart fields (straight table) can only show a text length of 65,535 characters. If the length of the text exceeds this size an empty textbox/field is shown which is a bit confusing. This can easily occur when a Concat aggregation is used.
View QlikView_Concat_Aggregation_Limitation.qvs
One should use a Concat aggregation on high amount of values with a left always:
=Left(Concat(DISTINCT Number,','), 65535)
View QlikView_Julian_Date_Conversion.qvs
// to convert from Julian Date to normal Date (Gregorian)
// subtract the offset 2415019 (equivalent to 30.12.1899, which is day 0 in QlikView):
Date(JulianDateField - 2415019)
//You probaly have to adjust the timezone since Julian Date is in UTC.
// to convert from normal Date (Gregorian) to Julian Date
// add the offset 2415019 (equivalent to 30.12.1899, which is day 0 in QlikView):
@ralfbecher
ralfbecher / Create_Persian_calender_with_Oracle.pls
Created Jun 10, 2014
Create a Persian calender with Oracle PL/SQL, in this case it creates a table of all dates from 30.12.1899 to 31.12.2099 for the use in QlikView
View Create_Persian_calender_with_Oracle.pls
CREATE TABLE "OCEAN"."T_DATE_PERSIAN"
(
"QV_DATE_NUM" NUMBER(6,0),
"GREGORIAN_DATE" DATE,
"PERSIAN_DATE" VARCHAR2(10 CHAR)
)
;
create or replace
PROCEDURE INSERT_T_DATE_PERSIAN IS
@ralfbecher
ralfbecher / QlikView_Calc_Days_per_Month_of_TimeSpans.qvs
Created Jun 13, 2014
Calculate days per month from time span events in QlikView
View QlikView_Calc_Days_per_Month_of_TimeSpans.qvs
Data:
LOAD * INLINE [
id, start_date, end_date
1234, 01.01.2014, 20.04.2014
5678, 23.02.2014, 25.03.2014
9012, 30.03.2014, 01.04.2014
];
Result:
NOCONCATENATE LOAD
@ralfbecher
ralfbecher / QlikView_Extension_Load_JavaScript_via_RequireJS_to_solve_UTF-8_issue.js
Last active Aug 29, 2015
QlikView Extension Load JavaScript via RequireJS to solve UTF-8 issue
View QlikView_Extension_Load_JavaScript_via_RequireJS_to_solve_UTF-8_issue.js
// thanks to Stefan Walther for giving a hint an this matter: https://github.com/stefanwalther
Qv.LoadExtensionScripts([_path + 'js/require.js'],
function () {
require([_path + 'js/d3.v3', _path + 'js/nv.d3.min', _path + 'js/interactiveLayer', _path + 'js/utils'],
function () {
Qv.AddExtension(_extension,
function () {
// render it..
@ralfbecher
ralfbecher / QlikView_get_and_format_microseconds.qvs
Last active Aug 29, 2015
QlikView get and format microseconds
View QlikView_get_and_format_microseconds.qvs
//Formula to optain microseconds from TimeField:
LOAD ((frac(TimeField) * 86400000) - floor(frac(TimeField) * 86400000)) * 1000 as Micro
FROM ...;
//Use this formula for formatting:
LOAD Timestamp(TimeField - (Micro/86400000000)) & Num(floor(Micro), '000') as TimeStamp
FROM ...;
@ralfbecher
ralfbecher / Neo4j-2.2_ajax_authentication_example.js
Created Mar 12, 2015
Neo4j 2.2 AJAX authentication example
View Neo4j-2.2_ajax_authentication_example.js
$.ajaxSetup({
headers: {
"Authorization": 'Basic ' + window.btoa(username+":"+password)
}
});
$.ajax(txUrl(), {
type: "POST",
data: JSON.stringify({
statements: [{
statement: query,
@ralfbecher
ralfbecher / QlikView_color_code_percentage_of_max_value.qvs
Created Aug 23, 2015
QlikView color code percentage of max value
View QlikView_color_code_percentage_of_max_value.qvs
=Replace(
ColorMix2 (if(rangemin(.90,rangemax( Sum(value) / Max({1} TOTAL value),.50))<.75,-Sqrt(-(rangemin(.90,rangemax( Sum(value) / Max({1} TOTAL value),.50))-.75)/(.75-.50)),Sqrt((rangemin(.90,rangemax( Sum(value) / Max({1} TOTAL value),.50))-.75)/(.90-.75))), RGB(255, 0, 0), RGB(0, 255, 0), RGB(255, 255, 0))
,'ARGB(255,','RGB(')
@ralfbecher
ralfbecher / Qlik_Sense_example_extension_with_switchable_user_custom_properties.js
Last active Sep 15, 2015
Qlik Sense example extension with switchable user custom properties
View Qlik_Sense_example_extension_with_switchable_user_custom_properties.js
/**
* @owner Ralf Becher, irregular.bi
*/
define( ["jquery", "qlik"],
function ($, qlik) {
return {
//property panel
definition: {
type: "items",
component: "accordion",
@ralfbecher
ralfbecher / QlikView_transform_matrix_into_adjacencylist.qvs
Last active Sep 26, 2015
QlikView transform matrix into adjacencylist
View QlikView_transform_matrix_into_adjacencylist.qvs
// matrix has numeric field names starting from "0" and a record number:
for i=0 to 34
for j=0 to 34
adj_list:
LOAD $(i) as node1,
$(j) as node2,
"$(j)" as measure
resident matrix
Where rec - 1= $(i) and "$(j)" > 0;
next
You can’t perform that action at this time.