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_Handle_Square_Brackets_in_Fieldnames.qvs
Last active December 25, 2015 03:09
Handle square brackets used in the field names (eg. CSV file) with QlikView. S olve it with a three step approach: 1. read field names as a list from the header row (transpose) into a mapping table and replace square brackets with paranthesis (or else) 2. load data with no labels (field names @1, @2 etc.) 3. rename field names using a mapping table
// load header row
Header:
FIRST 1 LOAD @1:n as First_Line
FROM flatfile.csv
(fix, codepage is 1252);
// load list of fields for renaming, replace square brackets with paranthesis
Map_Fields:
MAPPING LOAD '@' & RowNo() as Field1, Replace(Replace(SubField(First_Line, '|'), '[', '('), ']', ')') as Field2
Resident Header;
@ralfbecher
ralfbecher / QlikView_Load_Multiline_Header_Labels.qvw
Created December 11, 2013 10:05
QlikView Load Multiline Header Labels as Field Names from Excel (or CSV) source
/* source data (delimiter=tab), in file D:\header.xlsx:
Header 1 part a Header 2 part a Header 3 part a Header 4 part a Header 5 part a
Header 1 part b Header 2 part b Header 3 part b Header 4 part b Header 5 part b
Data Data Data Data Data
Data Data Data Data Data
Data Data Data Data Data
Data Data Data Data Data
resulting table (delimiter=tab):
Header 1 part a Header 1 part b Header 2 part a Header 2 part b Header 3 part a Header 3 part b Header 4 part a Header 4 part b Header 5 part a Header 5 part b
@ralfbecher
ralfbecher / QlikView_Load_Inline_Data_with_alternate_Delimiter.qvw
Created December 11, 2013 10:10
QlikView Load Inline Data with alternate Delimiter (eg. Pipe). This enables a possibility to copy/paste data from a deviate formatted CSV source without replacing the delimiters manually to comma..
INLINE:
Load * Inline
[
ENAME|SAL
SCOTT|800
SMITH|900
KIND|600
]
(ansi, txt, delimiter is '|', embedded labels)
;
@ralfbecher
ralfbecher / QlikView_Load_Inline_with_Data_containing_Square_Brackets.qvw
Last active December 31, 2015 00:29
QlikView Load Inline with Data containing Square Brackets
Load * Inline "
String
Abc
CDE
[c4]
";
@ralfbecher
ralfbecher / QlikView_Expression_to_format_Numbers_with_Factor_Denote.qvw
Created December 11, 2013 10:14
QlikView Expression to format Numbers with Factor Denote (eg. 100M, 200k)
=If(Value > 1E6, Round(Value/1E6, 1) & 'M',
If(Value > 1E3, Round(Value/1E3, 1) & 'k',
Value))
@ralfbecher
ralfbecher / QlikView_solving_slow_QVD_Load.qvw
Created December 11, 2013 13:51
QlikView solving slow QVD Load (not qvd optimized) with Where condition. A Load DISTINCT from QVD can be optimized too and is really fast (example would work without distinct although). So, this two-step approach is 2x faster.. The idea is to push the Where condition into a exists scenario.
/* Slow Load, not qvd optimized:
Bookings:
LOAD * From Bookings.qvd (qvd)
Where ID>0; // ..or with other condition: Not IsNull(ID)
*/
// qvd optimized
ExistingID:
LOAD DISTINCT ID From Bookings.qvd (qvd);
@ralfbecher
ralfbecher / QlikView_Concat_Aggregation_Limitation.qvs
Created February 16, 2014 15:02
QlikView textbox object and chart fields (straight table) can only show a text length of 65,535 characters. If the length of the text exceeds this size an empty textbox/field is shown which is a bit confusing. This can easily occur when a Concat aggregation is used.
One should use a Concat aggregation on high amount of values with a left always:
=Left(Concat(DISTINCT Number,','), 65535)
@ralfbecher
ralfbecher / QlikView_Julian_Date_Conversion.qvs
Created June 10, 2014 12:12
QlikView Julian Date Conversion
// to convert from Julian Date to normal Date (Gregorian)
// subtract the offset 2415019 (equivalent to 30.12.1899, which is day 0 in QlikView):
Date(JulianDateField - 2415019)
//You probaly have to adjust the timezone since Julian Date is in UTC.
// to convert from normal Date (Gregorian) to Julian Date
// add the offset 2415019 (equivalent to 30.12.1899, which is day 0 in QlikView):
@ralfbecher
ralfbecher / Create_Persian_calender_with_Oracle.pls
Created June 10, 2014 12:23
Create a Persian calender with Oracle PL/SQL, in this case it creates a table of all dates from 30.12.1899 to 31.12.2099 for the use in QlikView
CREATE TABLE "OCEAN"."T_DATE_PERSIAN"
(
"QV_DATE_NUM" NUMBER(6,0),
"GREGORIAN_DATE" DATE,
"PERSIAN_DATE" VARCHAR2(10 CHAR)
)
;
create or replace
PROCEDURE INSERT_T_DATE_PERSIAN IS
@ralfbecher
ralfbecher / QlikView_Calc_Days_per_Month_of_TimeSpans.qvs
Created June 13, 2014 13:22
Calculate days per month from time span events in QlikView
Data:
LOAD * INLINE [
id, start_date, end_date
1234, 01.01.2014, 20.04.2014
5678, 23.02.2014, 25.03.2014
9012, 30.03.2014, 01.04.2014
];
Result:
NOCONCATENATE LOAD