Ralf Becher ralfbecher

## 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
...

## 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

## 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>')

## 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

## 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>

## 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;

## 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;

## 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)


## 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)

## QlikView_WeekName_to_Date.qvs
// Convert weekname (eg. format YYYY/WW) to date:
=MakeWeekDate(subfield(field, '/', 1), subfield(field, '/', 2), 0)
	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
	...
	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
	// 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>')
	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
	// 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>
	// 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;
	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;
	//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)
	// 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)
	// Convert weekname (eg. format YYYY/WW) to date:
	=MakeWeekDate(subfield(field, '/', 1), subfield(field, '/', 2), 0)