Skip to content

Instantly share code, notes, and snippets.

Avatar

Ralf Becher ralfbecher

View GitHub Profile
View Qlik_Jira_Time_Formattings.qvs
// Format a time like in JIRA: 4w 3d 06:30
// means: 4 weeks + 3 days (1 day = 8 hrs) + 6 hrs + 30 mins
If(Floor(Sum(AllIssues_fields_aggregatetimeoriginalestimate)/28800)/5 >= 1,
Num(Floor(Floor(Sum(AllIssues_fields_aggregatetimeoriginalestimate)/28800)/5), '#0') & 'w ', '') &
Num(Frac(Sum(AllIssues_fields_aggregatetimeoriginalestimate)/28800/5) * 5, '#0') & 'd ' &
Interval(Frac(Sum(AllIssues_fields_aggregatetimeoriginalestimate)/28800)/3, 'hh:mm')
@ralfbecher
ralfbecher / QlikSenseClassicExtensionDebounceExample.js
Created May 9, 2017
A simple way to debounce in a paint() function of a classic (non-angular) QlikSense extension
View QlikSenseClassicExtensionDebounceExample.js
define([
"jquery",
"qlik",
"underscore"
],
function ($, qlik, _) {
'use strict';
return {
initialProperties: {},
@ralfbecher
ralfbecher / KeepFields.qvs
Created May 1, 2017
Qlik Script Sub KeepFields to Drop mass fields
View KeepFields.qvs
Sub KeepFields(Fields,TableName)
Let n = NoOfFields('$(TableName)');
Set d = #;
For i = 1 to $(n)
Let f = FieldName($(i), '$(TableName)');
If Index('$(Fields)', ',$(f),') = 0 then
If '$(d)' = '#' then
Let d = '$(f)';
Else
Let d = '$(d),$(f)';
@ralfbecher
ralfbecher / QlikView_WeekName_to_Date.qvs
Created Sep 2, 2013
Converts a given weekname (eg. 2013/36) to the starting date of the week. Opposite of QlikView WeekName() function.
View QlikView_WeekName_to_Date.qvs
// Convert weekname (eg. format YYYY/WW) to date:
=MakeWeekDate(subfield(field, '/', 1), subfield(field, '/', 2), 0)
@ralfbecher
ralfbecher / QlikView_Data_Sampling.qvs
Last active Jan 25, 2017
Load a random sampling data set for Data Profiling in QlikView.
View QlikView_Data_Sampling.qvs
// in this case we want to load a 10% sampling set of the data
data:
LOAD ….<your fields>…
FROM <your QVD file>
WHERE ceil(rand() * 100) <= 10; // or: rand() <= 0.1
// SAMPLE n LOAD is much faster but allocates the memory of the full data set during the LOAD
// Where rand() <= n allocates only the memory of the smaller result set which could be crucial with Big Data..
@ralfbecher
ralfbecher / QlikView_more_than_14_digits.qvs
Created Sep 13, 2013
Loading numbers in QlikView with more than 14 digits leads into scientific (exponential) notation. The 64-bit IEEE float can only show 14 digits.
View QlikView_more_than_14_digits.qvs
LOAD Div(Field, 1e10) & left(repeat('0',10), 10 - len(text(Mod(Field, 1e10)))) & Mod(Field, 1e10) as MyNum
@ralfbecher
ralfbecher / QlikView_Wrap_Conditional_View_Expression.qvs
Last active Jan 25, 2017
In QlikView conditional show expressions cannot handle NULL values. The object will be visible because NULL is treated as TRUE (or the expression doesn't evaluate right).
View QlikView_Wrap_Conditional_View_Expression.qvs
// In this case it is helpful to wrap the expression to catch NULL and empty string:
// works for:
// =if(Null(), -1, 0)
// =if('', -1, 0)
=if(<expression>, -1, 0)
@ralfbecher
ralfbecher / QlikView_Load_broken_CSV.qvs
Created Sep 11, 2013
Load data from text or CSV file with broken format where a field contains CR/LF.
View QlikView_Load_broken_CSV.qvs
/* Example file 'broken.txt' with a broken line, field containing CR/LF:
Field1|Fiel2|Field3
Abcd|efg|hijk
Lm|no
pq|vwz
123|456|7890
*/
Set vDelimiter = '|';
Set vNoOfColumns = 3;
@ralfbecher
ralfbecher / QlikView_Read_SheetIDs.qvs
Last active Jan 25, 2017
Read sheet ID and name of a QlikView document
View QlikView_Read_SheetIDs.qvs
Set vDocument=C:\Projekte\QlikView\MeineApp.qvw;
Sheets:
LOAD '$(vDocument)' as DocumentName,
SubField(SheetId, '\', 2) as SheetID,
Title as SheetName
FROM [$(vDocument)] (XmlSimple, Table is [DocumentSummary/Sheet]);
@ralfbecher
ralfbecher / QlikView_Patch_ARGB_to_RGB.qvs
Last active Jan 25, 2017
QlikView ColorMix function cannot be used in SVG extension color expression because it returns ARGB(), alpha is not usable for SVG path (use opacity property instead). This shows how to patch the return to RGB() code if alpha is a constant value.
View QlikView_Patch_ARGB_to_RGB.qvs
// patch ARGB() to RGB():
=replace( ColorMix1( ... ) , 'ARGB(255,', 'RGB(' )
You can’t perform that action at this time.