Page tree
Skip to end of metadata
Go to start of metadata

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 priceGroup 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 priceForce filter by country=US or FR and group by year.

NATIVE('QLIK(mytable.price, ''TOTAL <mytable.year> {<mytable.country={US,FR}>}'')')

6) Aggregation of priceIgnore state and city filter, Group by country and state.

NATIVE('QLIK(mytable.price, ''TOTAL <mytable.country,mytable.state> {<mytable.state = , mytable.city = >}'')')

Recommended

 To view an example of a full app's script, click here.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';

SET DirectConnectionMax=10;
SET DirectCacheSeconds=10;
SET DirectTableBoxListThreshold  = 5000;

LIB CONNECT TO 'JethroODBC';

DIRECT QUERY
dimension
   customer_ds.c_salutation as customer_salutation, 
   customer_ds.c_first_name as customer_first_name, 
   customer_ds.c_last_name as customer_last_name,
   customer_ds.c_preferred_cust_flag as customer_preferred_flag,
   customer_ds.c_birth_year as customer_birth_year,
   customer_ds.c_birth_country as customer_birth_country,
   customer_demographics.cd_gender as customer_gender, 
   customer_demographics.cd_marital_status as customer_marital_status, 
   customer_demographics.cd_education_status as customer_education_status,
   store.s_store_name as store_name,
   store.s_city as store_city, 
   store.s_hours as store_hours,
   store.s_county as store_county,
   store.s_state as store_state,
   store.s_country as store_country,
   item.i_product_name as item_product_name,
   item.i_class as item_class,
   item.i_category as item_category,
   item.i_size as item_size,
   item.i_color as item_color,
   date_dim.d_date as sale_date, 
   date_dim.d_year as sale_year,
   date_dim.d_holiday as sale_is_holiday, 
   date_dim.d_weekend as sale_is_weekend

measure
   store_sales.ss_quantity as quantity, 
   store_sales.ss_list_price as list_price,
   store_sales.ss_net_paid as net_paid,
   store_sales.ss_net_profit as net_profit,
   NATIVE('QLIK(store_sales.ss_quantity, ''TOTAL <store.s_state>'')') AS QTY_PER_STATE,
   NATIVE('QLIK(store_sales.ss_net_profit, ''TOTAL <store.s_state>'')') AS PROFIT_PER_STATE,
   NATIVE('QLIK(store_sales.ss_quantity, ''TOTAL <store.s_state> {<item.i_size=,item.i_color=,item.i_class=,item.i_category=,store.s_store_name=>} '')') AS QTY_PER_STATE_IGNOR_FILTERS,
   NATIVE('QLIK(store_sales.ss_quantity, ''TOTAL {<item.i_color=,item.i_class=,item.i_category=,store.s_store_name=>} '')') AS QTY_TOTAL_SIZE_FILTER,
   NATIVE('QLIK(store_sales.ss_quantity, ''TOTAL {<item.i_color=,item.i_size=,item.i_class=,item.i_category=,store.s_store_name=,store.s_state=,date_dim.d_year=,store.s_county=>} '')') AS QTY_TOTAL_IGNOR_FILTERS,
   NATIVE('QLIK(store_sales.ss_net_profit, ''TOTAL {<store.s_state={TX}>} '')') AS NET_PROFIT_FOR_TX,
   NATIVE('QLIK(store_sales.ss_net_profit, ''TOTAL {<date_dim.d_year={2002}>} '')') AS Current_Year_Profit,
   NATIVE('QLIK(store_sales.ss_net_profit, ''TOTAL {<date_dim.d_year={2001}>} '')') AS Prior_Year_Profit

detach
   store_sales.ss_store_sk as store_sk,
   store_sales.ss_item_sk as item_sk,
   store_sales.ss_customer_sk as customer_sk
 FROM store_sales
 inner join store       on (store_sales.ss_store_sk     = store.s_store_sk)
 inner join item        on (store_sales.ss_item_sk      = item.i_item_sk)
 inner join date_dim    on (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
 inner join customer_ds on (store_sales.ss_customer_sk  = customer_ds.c_customer_sk)
 inner join customer_demographics on (store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk)
 ;

See also

How to connect Qlik Sense to Jethro

  • No labels