View QlikView_Networkdays_including_Saturday.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
... |
View QlikView_Calculate_Age_Group.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
View QlikView_Convert_from_Unix_Timestamp.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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>') |
View QlikView_Load_Sampling_Data.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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> |
View QlikView_Load_Fieldnames_with_Brackets.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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; |
View QlikView_Convert_into_Unix_Timestamp.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
View QlikView_Calculate_Benford_s_Law.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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) | |
View QlikView_Straight_Table_on_Fields.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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) |
View QlikView_WeekName_to_Date.qvs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Convert weekname (eg. format YYYY/WW) to date: | |
=MakeWeekDate(subfield(field, '/', 1), subfield(field, '/', 2), 0) |
OlderNewer