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
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 |
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
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 |
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
// 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): |
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
One should use a Concat aggregation on high amount of values with a left always: | |
=Left(Concat(DISTINCT Number,','), 65535) |
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
/* 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); |
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
=If(Value > 1E6, Round(Value/1E6, 1) & 'M', | |
If(Value > 1E3, Round(Value/1E3, 1) & 'k', | |
Value)) |
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 * Inline " | |
String | |
Abc | |
CDE | |
[c4] | |
"; |
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
INLINE: | |
Load * Inline | |
[ | |
ENAME|SAL | |
SCOTT|800 | |
SMITH|900 | |
KIND|600 | |
] | |
(ansi, txt, delimiter is '|', embedded labels) | |
; |
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
/* 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 |
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 for renaming, replace square brackets with paranthesis | |
Map_Fields: | |
MAPPING LOAD '@' & RowNo() as Field1, Replace(Replace(SubField(First_Line, '|'), '[', '('), ']', ')') as Field2 | |
Resident Header; |