Qlik Functions
- Boaz Raufman
Qlik() is an exclusive Jethro function that imitates Set Analysis and TOTAL expressions, which are commonly used in Qlik's BI tools (Qlikview & Qliksense).
Syntax
QLIK(table-name.column-name, '{[<qlik-total-expression>] [<qlik-set-analysis-expression>]}')
<qlik-total-expression>::=
TOTAL [<table-name.column-name [,table-name.column-name...]>]
<qlik-set-analysis-expression> ::=
table-name.column-name=[value [,value...]] [,table-name.column-name=[value [,value...]]...]
Description
The Qlik() function was added to Jethro as a workaround solution for a limitation in Qlik's Direct Discovery (Set Analysis is not supported in DD mode). The way Jethro enabled the use of this functionality in DD, is by sending it directly to Jethro, through the app's script, as a NATIVE('') database expression (see the examples below).
This means that Set Analysis and TOTAL expressions would become available for use in DD mode, but it would not be generated based on free formulas written inside Qlik's objects. Only pre-defined Qlik() expressions in the app's script would be able to support these functionalities.
Please note that when using Qlik's NATIVE('') method in the script, it is required to use double apostrophes instead of single apostrophes (It may look like double quotes in the examples, but it's not - it's double apostrophes).
Examples
The examples below are for Direct Discovery script expressions. To use the Qlik function in JethroClient (not through Qlik tools), remove the NATIVE(' ') part of each example, replace each double apostrophes with a single apostrophe, and make sure to add an aggregation before the function itself.
For example: select sum(qlik(item.i_category_id,'TOTAL')) from item;
1) Aggregation of price for an entire data set:
NATIVE('QLIK(mytable.price, ''TOTAL'')')
2) Aggregation of price, Group by year:
NATIVE('QLIK(mytable.price, ''TOTAL <mytable.year>'' )')
3) Aggregation of price, Ignore filters on city:
NATIVE('QLIK(mytable.price, ''{<mytable.city=>}'')')
4) Aggregation of price, Force filter by country=US
NATIVE('QLIK(mytable.price, ''{<mytable.country={US}>}'')')
5) Aggregation of price, Force filter by country=US or FR and group by year.
NATIVE('QLIK(mytable.price, ''TOTAL <mytable.year> {<mytable.country={US,FR}>}'')')
6) Aggregation of price. Ignore state and city filter, Group by country and state.
NATIVE('QLIK(mytable.price, ''TOTAL <mytable.country,mytable.state> {<mytable.state = , mytable.city = >}'')')
Recommended