Peeking through the Window() Script Function

[Ouadie]

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

Window Function Examples.qvf