Skip to content

Instantly share code, notes, and snippets.

View ralfbecher's full-sized avatar

Ralf Becher ralfbecher

View GitHub Profile
@ralfbecher
ralfbecher / QlikView_Read_SheetIDs.qvs
Last active January 25, 2017 16:17
Read sheet ID and name of a QlikView document
Set vDocument=C:\Projekte\QlikView\MeineApp.qvw;
Sheets:
LOAD '$(vDocument)' as DocumentName,
SubField(SheetId, '\', 2) as SheetID,
Title as SheetName
FROM [$(vDocument)] (XmlSimple, Table is [DocumentSummary/Sheet]);
@ralfbecher
ralfbecher / QlikView_Patch_ARGB_to_RGB.qvs
Last active January 25, 2017 16:17
QlikView ColorMix function cannot be used in SVG extension color expression because it returns ARGB(), alpha is not usable for SVG path (use opacity property instead). This shows how to patch the return to RGB() code if alpha is a constant value.
// patch ARGB() to RGB():
=replace( ColorMix1( ... ) , 'ARGB(255,', 'RGB(' )
@ralfbecher
ralfbecher / QlikView_Show_non-selected_Values.qvs
Created September 10, 2013 09:06
Show non-selected values in a listbox.
// This expressions gives all non-selected values
=aggr(only({1-$} Field), Field)
@ralfbecher
ralfbecher / QlikView_Generic_Load_Example_Table_Consolidation.qvs
Last active March 6, 2021 13:33
Generic load example from QlikView Reference Manual with table consolidation.
// generic load with one consolidated resulting table:
InputTable:
LOAD * INLINE [
object,attribute,value
ball,color,red
ball,diameter,10 cm
ball,weight,100 g
box,color,black
box,height,16 cm
box,length,20 cm
@ralfbecher
ralfbecher / QlikView_Unnest_Field_Data_While.qvs
Created September 10, 2013 12:10
Unnest dynamic amount of data stored in field value using LOAD While:
// unnest data of field value:
Input:
LOAD * INLINE [
ACCOUNT, SUPPLIES
001, "Apples,Bananas,Tomatos"
002, "Bread,Butter,Cheese"
003, "Pretzel,Wuerstl"
];
Result:
@ralfbecher
ralfbecher / QlikView_List_of_Values_Variable.qvs
Created September 11, 2013 20:45
Create a list of values variable for the usage in a SQL Where IN-Clause.
Companies:
LOAD * INLINE [
Company
CompanyA
CompanyB
CompanyC
];
List:
LOAD
@ralfbecher
ralfbecher / QlikView_Load_broken_CSV.qvs
Created September 11, 2013 21:42
Load data from text or CSV file with broken format where a field contains CR/LF.
/* Example file 'broken.txt' with a broken line, field containing CR/LF:
Field1|Fiel2|Field3
Abcd|efg|hijk
Lm|no
pq|vwz
123|456|7890
*/
Set vDelimiter = '|';
Set vNoOfColumns = 3;
@ralfbecher
ralfbecher / QlikView_Wrap_Conditional_View_Expression.qvs
Last active January 25, 2017 16:17
In QlikView conditional show expressions cannot handle NULL values. The object will be visible because NULL is treated as TRUE (or the expression doesn't evaluate right).
// In this case it is helpful to wrap the expression to catch NULL and empty string:
// works for:
// =if(Null(), -1, 0)
// =if('', -1, 0)
=if(<expression>, -1, 0)
@ralfbecher
ralfbecher / QlikView_more_than_14_digits.qvs
Created September 13, 2013 13:09
Loading numbers in QlikView with more than 14 digits leads into scientific (exponential) notation. The 64-bit IEEE float can only show 14 digits.
LOAD Div(Field, 1e10) & left(repeat('0',10), 10 - len(text(Mod(Field, 1e10)))) & Mod(Field, 1e10) as MyNum
@ralfbecher
ralfbecher / QlikView_Data_Sampling.qvs
Last active January 25, 2017 16:18
Load a random sampling data set for Data Profiling in QlikView.
// in this case we want to load a 10% sampling set of the data
data:
LOAD ….<your fields>…
FROM <your QVD file>
WHERE ceil(rand() * 100) <= 10; // or: rand() <= 0.1
// SAMPLE n LOAD is much faster but allocates the memory of the full data set during the LOAD
// Where rand() <= n allocates only the memory of the smaller result set which could be crucial with Big Data..