Peeking through the Window() Script Function - Qlik Community

Ouadie · ‎2024-01-05

You might be familiar with the concept of Window functions from Excel or SQL and know just how convenient and powerful they can be. Well, Qlik has one that you can use right in your Load Script!

Simply put, the Window function performs calculations over multiple rows producing a value for each row separately, unlike aggregate functions that will give a single value for the group of rows aggregated together.

You can think of it as looking through a window at your dataset and only seeing a subset based on different parameters you set which we will go over in a minute.

If you wanted to calculate the average transaction_amount by customer, you could of course do this in the chart expression with something like this: aggr(avg(transaction_amount), customer_id), or if you’re in the Load Script, perform another load and use Group By as follows:

Temp:
//inline load here

Transactions:
NoConcatenate Load
transaction_id,
transaction_date,
transaction_amount,
transaction_quantity,
customer_id,
size,
color_code
Resident Temp;

Load customer_id,
Avg(transaction_amount) AS AvgAmount
Resident Transactions
Group By customer_id;

But this requires a separate load and can’t just be done on the same loaded table, and it might not be ideal for more complex use cases.

This is where the Window function comes in, and the above can be re-written as follows:

Temp:
//inline load here

Transactions:
NoConcatenate Load
transaction_id,
transaction_date,
transaction_amount,
transaction_quantity,
customer_id,
size,
color_code,
Window(Avg(transaction_amount),customer_id) as AvgCustTransaction
Resident Temp;

Much easier!

Syntax:

Let’s take a closer look at the function syntax to understand it a little more and see what other capabilities it has:

Window( input_expr, [partition1, partition2, ...], [sort_type, [sort_expr]], [filter_expr], [start_expr,end_expr] )

input_expr

Refers to the input expression calculated and returned by the function. It must be any expression based on an aggregation, such as Median(Salary). For example:

Window(Median(Salary)) as MedianSalary

The input can also be a field name with no aggregation applied and in that case Qlik treats it like the Only() function. For example:

Window(Salary,Department) as WSalary

Partition: [partition1, partition2, ...]

After input_expr, you can define any number of partitions. Partitions are fields that define which combinations to apply the aggregations with. The aggregation is applied separately with each partition. (Think of it as the Group By clause). Multiple partitions can be defined. For example:

Window(Avg(Salary), Unit, Department, Country) as AvgSalary

sort_type, sort_expr

The sort type and the sort expression can be specified optionally. sort_type can have one of two values ASC (Ascending sorting) or DESC (Descending sort)

If sort_type is defined, then the sorting expression must also be defined. This is an expression that decides the order of the rows within a partition.

For example:

Window(RecNo(), Department, 'ASC', Year)
// results within the partition are sorted Ascendingly by year

filter_expr

The optional Filter Expression is a Boolean expression that decides whether the record should be included in the calculation or not.

This parameter can be omitted completely, and the result should be that there is no filter.

For example:

Window(avg(Salary), Department, 'ASC', Age, EmployeeID=3 Or EmployeeID=7) as wAvgSalaryIfEmpIs3or7

Sliding Window with start_expr,end_expr

Optionally, you can set the argument for sliding window functionality. A sliding window requires two arguments:

start_expr: The number of rows prior to the current row to include in the window.
end_expr: The number of rows after the current row to include in the window.

For example, if you want to include the 3 preceding rows, the current row, and the 2 following row:

Window(concat(Text(Salary),'-'), Department, 'ASC', Age, Year>0,  -3, 2) as WSalaryDepartment

Examples:

Let’s take a look at different use case examples:

1- Adding a field containing an aggregation

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id) as AvgCustTransaction;

Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, M, Orange
3752, 20180916, 5.75, 1, 5646471, S, Blue
3753, 20180922, 125.00, 7, 3036491, L, Black
3754, 20180922, 484.21, 13, 049681, XS, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
3758, 20180924, 153.42, 14, 2038593, L, Red
3759, 20180925, 7.42, 5, 203521, M, Orange
3760, 20180925, 80.12, 18, 5646471, M, Blue
3761, 20180926, 3.42, 7, 3036491, XS, Black
3763, 20180926, 63.55, 12, 049681, S, Red
3763, 20180927, 177.56, 10, 2038593, L, Blue
3764, 20180927, 325.95, 8, 203521, XL, Black
];

2- Adding a field containing an aggregation filtered for specific values

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, color_code = 'Blue') as AvgCustTransaction;

Load * Inline [
// Table goes here
];

3- Adding a field with a sliding window

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, 'ASC', -1, 1, 0, 1) as AvgCustTransaction;

Load * Inline [
// Table goes here
];

This concludes this post, I hope you found it helpful!
A qvf with all the scripts is attached for reference.

- Thanks

to759_PM · ‎2024-01-08

@Ouadie greate explanation of the new greate function in Novemember 2023 release of Qlik Sense Enterprise on Windows , but anyhow how to get it run
I'm using November 2023 Patch 2 (14.159.6) as Test-Version and also as Qlik Sense Desktop and syntax highlighting shows an error and also script reload failed. Are there any limitation not registered, yet

marcus_sommer · ‎2024-01-08

Is the window() feature a new technology in regard to aggregations in Qlik or is it more a kind of usability to simplify and shorten script-code by hiding classical group by aggregations and/or any interrecord-logic for the users? How does it work?

starke_be-terna · ‎2024-01-09

Hi @Ouadie !

I see the potential of this function making code easier to read, but I am not so sure about performance.

We tested this function with different scenarios and tried to evaluate if it is faster or slower than performing the corresponding aggregation using GROUP BY. All scenarios we could think of were slower when using window().

Is there a scenario / example in which window() would lead to better performance that you could provide? Or maybe our first assessment ist correct?

to759_PM · ‎2024-01-09

@starke_be-terna which product Qlik Sense Enterprise on Windows , Qlik Sense Desktop or Qlik Cloud did you use and if QSoW or Desktop which Version?

Thanks!

starke_be-terna · ‎2024-01-09

@to759_PM we tested it on Qlik Cloud and Qlik Sense Desktop November 2023. I am pretty sure, that our data would be the same with Qlik Sense On-Premise.