Skip to content

Instantly share code, notes, and snippets.

Ralf Becher ralfbecher

Block or report user

Report or block ralfbecher

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@ralfbecher
ralfbecher / QlikView_Networkdays_including_Saturday.qvs
Last active Dec 12, 2015
Calculate Networkdays with Saturday included in QlikView
View QlikView_Networkdays_including_Saturday.qvs
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 Dec 15, 2015
Calculate age-group in QlikView
View QlikView_Calculate_Age_Group.qvs
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 Dec 15, 2015
Convert from a Unix timestamp to the local time in QlikView
View QlikView_Convert_from_Unix_Timestamp.qvs
// 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 Dec 15, 2015
Strip HTML tags in field data during QlikView load using a mapping table and MapSubstring() function
View QlikView_Strip_HTML_Tags.qvs
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 Dec 15, 2015
Load a random sampling data set for Data Profiling in QlikView
View QlikView_Load_Sampling_Data.qvs
// 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 Apr 4, 2013
Load flat file (CSV or else) with field names containing square brackets "[ ]" into QlikView
View QlikView_Load_Fieldnames_with_Brackets.qvs
// 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 Dec 15, 2015
Convert QlikView local time into a Unix timestamp
View QlikView_Convert_into_Unix_Timestamp.qvs
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 Aug 9, 2018
Calculate Benford's Law in QlikView
View QlikView_Calculate_Benford_s_Law.qvs
//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 Dec 22, 2015
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…
View QlikView_Straight_Table_on_Fields.qvs
// 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 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)
You can’t perform that action at this time.