Skip to content

Instantly share code, notes, and snippets.

View ralfbecher's full-sized avatar

Ralf Becher ralfbecher

171 followers · 112 following
View GitHub Profile
@ralfbecher
ralfbecher / QlikView_Networkdays_including_Saturday.qvs
Last active December 12, 2015 08:08
Calculate Networkdays with Saturday included in QlikView
Load
floor(((Date2 - Date1)+1)/7)*6 + mod((Date2 - Date1)+1,7)
+ if(Weekday(Date1) + mod((Date2 - Date1)+1,7) < 7, 0, -1) as Workingdays_6
...
@ralfbecher
ralfbecher / QlikView_Calculate_Age_Group.qvs
Last active December 15, 2015 19:49
Calculate age-group in QlikView
LOAD
if(age<16, 'Under 16',
if(age>65, 'Over 65',
text(16+(Div(age-16,10)*10)) & ' - ' & text(25+(Div(age-16,10)*10)))) as age_group
@ralfbecher
ralfbecher / QlikView_Convert_from_Unix_Timestamp.qvs
Last active December 15, 2015 19:58
Convert from a Unix timestamp to the local time in QlikView
// Solution 1:
Timestamp = ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + (<timestamp_field> / 86400)), '<time_zone>')
// Solution 2:
Timestamp = ConvertToLocalTime(Date(25569 + (<timestamp_field> / 86400)), '<time_zone>')
@ralfbecher
ralfbecher / QlikView_Strip_HTML_Tags.qvs
Last active December 15, 2015 19:58
Strip HTML tags in field data during QlikView load using a mapping table and MapSubstring() function
Data:
LOAD RecNo() as RecNo, Field
FROM <your source file>;
HtmlTag_Map:
MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(Field, '<', IterNo()),'<','>') & '>' as HtmlTag, '' as Substitute
Resident Data
While NOT IsNull(SubField(Field, '<', IterNo()));
JOIN(Data) LOAD RecNo, MapSubstring('HtmlTag_Map', Field) as Field_Cleansed
@ralfbecher
ralfbecher / QlikView_Load_Fieldnames_with_Brackets.qvs
Created April 4, 2013 22:08
Load flat file (CSV or else) with field names containing square brackets "[ ]" into QlikView
// load header row
Header:
FIRST 1 LOAD @1:n as First_Line
FROM flatfile.csv
(fix, codepage is 1252);
// load list of fields from source for renaming, replace square brackets with paranthesis (or something else),
// delimiter is '|'
Map_Fields:
MAPPING LOAD '@' & RowNo() as Field1, Replace(Replace(SubField(First_Line, '|'), '[', '('), ']', ')') as Field2;
@ralfbecher
ralfbecher / QlikView_Load_Sampling_Data.qvs
Last active December 15, 2015 19:59
Load a random sampling data set for Data Profiling in QlikView
// In this case we want to load a 10% sampling set of the data
// The QlikView way:
data:
Sample(0.1) LOAD <your fields>
FROM <your source file>
// My way, using random function:
data:
LOAD <your fields>
FROM <your source file>
@ralfbecher
ralfbecher / QlikView_Convert_into_Unix_Timestamp.qvs
Last active December 15, 2015 19:59
Convert QlikView local time into a Unix timestamp
LET t1 = (num(timestamp#('2013-01-11 18:00:00', 'yyyy-mm-dd hh:mm:ss')) - 25569) * 86400;
LET t2 = (num(Now(1) - 25569) * 86400;
@ralfbecher
ralfbecher / QlikView_Straight_Table_on_Fields.qvs
Last active December 22, 2015 03:29
Having a straight table chart in QlikView with $Field as dimension to count the occurances of values per field. The given expression creates a nested if()-function like this: if($Field='FieldName1',count(FieldName1),if($Field='FieldName2',count(FieldName2), ... etc. Hint: Concat() aggregation function seems to be the only one which works with $F…
// expression for count($Field), no leading '=':
$(=concat(TOTAL 'if($Field=' & chr(39) & $Field & chr(39) & ',count([' & $Field & '])', ',') & concat(TOTAL right($Field&')',1)))
// expression for count(DISTINCT $Field):
=FieldValueCount($Field)
@ralfbecher
ralfbecher / QlikView_Handle_Square_Brackets_in_Fieldnames.qvs
Last active December 25, 2015 03:09
Handle square brackets used in the field names (eg. CSV file) with QlikView. S olve it with a three step approach: 1. read field names as a list from the header row (transpose) into a mapping table and replace square brackets with paranthesis (or else) 2. load data with no labels (field names @1, @2 etc.) 3. rename field names using a mapping table
// load header row
Header:
FIRST 1 LOAD @1:n as First_Line
FROM flatfile.csv
(fix, codepage is 1252);
// load list of fields for renaming, replace square brackets with paranthesis
Map_Fields:
MAPPING LOAD '@' & RowNo() as Field1, Replace(Replace(SubField(First_Line, '|'), '[', '('), ']', ')') as Field2
Resident Header;
@ralfbecher
ralfbecher / QlikView_Load_Multiline_Header_Labels.qvw
Created December 11, 2013 10:05
QlikView Load Multiline Header Labels as Field Names from Excel (or CSV) source
/* source data (delimiter=tab), in file D:\header.xlsx:
Header 1 part a Header 2 part a Header 3 part a Header 4 part a Header 5 part a
Header 1 part b Header 2 part b Header 3 part b Header 4 part b Header 5 part b
Data Data Data Data Data
Data Data Data Data Data
Data Data Data Data Data
Data Data Data Data Data
resulting table (delimiter=tab):
Header 1 part a Header 1 part b Header 2 part a Header 2 part b Header 3 part a Header 3 part b Header 4 part a Header 4 part b Header 5 part a Header 5 part b