Skip to content

Instantly share code, notes, and snippets.

Avatar

Ralf Becher ralfbecher

View GitHub Profile
@ralfbecher
ralfbecher / QlikView_Expression_NewProductPerYear.qvs
Last active Oct 21, 2015
QlikView Chart Expression Counts New Products/Year
View QlikView_Expression_NewProductPerYear.qvs
data:
LOAD * INLINE [
Year, Product
2010, P1
2011, P1
2012, P1
2010, P2
2011, P2
2011, P3
2012, P3
@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_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_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_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_Haversine_formula.qvs
Created Dec 16, 2015
QlikView Haversine formula calculation
View QlikView_Haversine_formula.qvs
// calculation of distance of two geo references (lat/lon):
= 12742 * atan2(sqrt(sqr(sin(((lat2-lat1)*PI()/180)/2))
+ (cos(lat1*PI()/180) * cos(lat2*PI()/180))
* pow(sin(((lon2-lon1)*PI()/180)/2),2)),
sqrt(1 - (sqr(sin(((lat2-lat1)*PI()/180)/2))
+ (cos(lat1*PI()/180) * cos(lat2*PI()/180))
* pow(sin(((lon2-lon1)*PI()/180)/2),2))))
@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)
You can’t perform that action at this time.