Qlik qvs formulae

autogenerate-demo-sample.qvs

// AutoGenerate() demo-sample
t:
NoConcatenate LOAD
    10 + RowNo() - 1 as t
    , RecNo() as g
    , (6 + RecNo()) * 1e3 + IterNo() - 1 as v
AutoGenerate 7 While IterNo() <= 10;

custom-distinct.qvs

_some_table:
LOAD
    * // _hash_for_some_attrs comes from there
From [_some_table.qvd];

Concatenate LOAD * Where not Exists(_hash_for_some_attrs);
LOAD
    // other, ...
    , dup_avoid_1
    , dup_avoid_2
    // ..., other
    // -- additions by hereby ETL
    , Hash256(dup_avoid_1, dup_avoid_2) as _hash_for_some_attrs
From [_some_table_datasource];

STORE _some_table into [_some_table.qvd]; DROP Table _some_table;


// -----------------------------------------------
// Another demo-sample

t:
NoConcatenate
LOAD *, Hash256(a, b) as _h;
LOAD * Inline [
a, b, v
1, 1, t-v-1
2, 2, t-v-2
3, 2, t-v-3
];

Concatenate
LOAD * Where not Exists(_h, Hash256(a, b));
LOAD * Inline [
a, b, v
1, 1, x-1
2, 1, x-2
3, 1, x-3
1, 2, x-4
2, 2, x-5
3, 2, x-6
];

DROP Field _h;

drop-all-tables.qvs

// iterate over tables - cleanup
FOR i = 1 to NoOfTables()
    LET _T = TableName(0);
    DROP Table [$(_T)];
NEXT i
i = ; _T = ;

export-csv-bz2.qvs

SUB ExportCsv(t, fn)
    EXIT SUB when IsNull(t);
    TRACE [ExportCsv] CALLED for [$(t)];

    EXIT SUB when IsNull(fn) and IsNull(vExportPath);
    fn = If(not IsNull(fn), '$(fn)', '$(vExportPath)\$(t).csv');
    TRACE [ExportCsv] will dump to [$(fn)];
    STORE [$(t)] into [$(fn)] (txt);

    // bzip2 is data.table::fread() friendly (and gives better compression than gz)
    EXECUTE C:\opt\rtools40\usr\bin\bzip2.exe -fz1 "$(fn)";

    TRACE [ExportCsv] DONE;
END SUB


// // Usage example:
// LET vExportPath='C:\tmp';
// tx:
// LOAD RowNo() AutoGenerate(1e3);
// CALL ExportCsv('tx');

sortedvals-setexpr-asof-samples.qvs

// Sample data
LOAD *, Div(ts, 10) as tf;
LOAD *
Inline [
    prog, app,  ts,  v
       Q,   c,  10,  0.5
       P,   a,  21,  0.3
       P,   b,  22,  1
       Q,   c,  23,  0.2
       P,   a,  24,  3
       Q,   c,  25,  2
       P,   b,  36,  0.1
];

// This expression gives the "last known value [v]" (sorted by timestamps [ts])
//  for every entity [app], for the whole period <= selected timeframe [tf].
FirstSortedValue( {<tf={'<=$(tf)'}>} DISTINCT TOTAL <app,tf> v, -ts)


// -----------------------------------------------
// as-of table sample

tf_aotf_2:
LOAD
    tf,
    tf + IterNo() - 1 as aotf2
Resident main_data
While IterNo() <= 2 and tf + IterNo() - 1 <= 3
;