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_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_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_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_Calculate_Benford_s_Law.qvs
Last active August 9, 2018 18:45
Calculate Benford's Law in QlikView
//QlikView Script, Load the distribution of first digits, according to Benford's law, as Data Island:
Benford:
LOAD FirstDigit, log10(FirstDigit+1) - log10(FirstDigit) as OccurancePct;
LOAD RecNo() as FirstDigit AutoGenerate(9);
//***********************************************************************************
//Chart
//Dynamic Dimension;
=ValueList(1,2,3,4,5,6,7,8,9)
@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_WeekName_to_Date.qvs
Created September 2, 2013 10:34
Converts a given weekname (eg. 2013/36) to the starting date of the week. Opposite of QlikView WeekName() function.
// Convert weekname (eg. format YYYY/WW) to date:
=MakeWeekDate(subfield(field, '/', 1), subfield(field, '/', 2), 0)