Happy New Year!
Let’s face it — many of Qlik developers know SQL.
Is knowing SQL(Structured Query Language) essential to become a Qlik developer? Maybe.
It’s a two-edged sword — SQL knowledge gives them a competitive advantage over someone who has no technical experience. Still, it can also pose some challenges when you want to use some of your favorite SQL functions and clauses while learning nuances of Qlik syntax.
Synopsis
In this blog post, I want to explore a SQL function coalesce() to show you how to use a relatively obscure yet similar function in Qlik – ALT function.
All SQL developers know how to use HAVING BY clause and wonder how you can use it as a Qlik developer? Worry not!
We will explore ways to make that happen it Qlik with finesse.
Coalesce() Function
This function returns the first Non-null value from a list.
For example,
SELECT COALESCE(NULL,NULL, ‘Analyticshub.io’, NULL);
How will you write a chart expression to mimic coalesce()?
You are a Qlik Sense Designer, and you are dealing with a problem to replace null values in a sales table with a specific value: 500.
It seems like a tall order, right?
Enter the ALT() function in Qlik.
ALT() function also returns the first of the parameters with a valid numeric representation.
Let’s create a simple Inline table to understand how ALT() function works.
Orders:
load * inline [
Sales
800
1200
null()
500
Null()
]
;
Use this expression in the chart.
Sum(alt(Sales, 500))
If you create a KPI to show Sales, you will see $3500 instead of $2500 because of the magic of ALT() function. It replaces every null value with the first numeric value in the expression.
HAVING CLAUSE
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
source: W3Schools.com
Let’s look at an example.
I have an order details table, and I wanted to list orders and their respective sales totals.
In SQL, I can run a SQL query as follows:
SELECT ORDER_ID, SUM(UNIT_PRICE * QUANTITY) AS ORDER_TOTAL
FROM DEMO_ORDER_ITEMS
GROUP BY ORDER_ID
Now, if I want to eliminate orders with less than $1000, I can use HAVING BY clause after GROUP BY to filter rows.
SELECT ORDER_ID, SUM(UNIT_PRICE * QUANTITY) AS ORDER_TOTAL
FROM DEMO_ORDER_ITEMS
GROUP BY ORDER_ID
HAVING SUM(UNIT_PRICE * QUANTITY) > 1000
How will you achieve this as a Qlik developer?
You will have to use the power of the preceding load to make that work in Qlik.
If I bring this order details table in my Qlik Sense application, I can write a script as follows to mimic HAVING BY clause.
Orders:
LOAD
*
WHERE SALES_TOTAL > 1000
;
LOAD
sum(Sales) AS SALES_TOTAL, ORDER_ID
group by ORDER_ID
;
LOAD
[ORDER_ITEM_ID],
[ORDER_ID],
[PRODUCT_ID],
[UNIT_PRICE],
[QUANTITY],
[UNIT_PRICE] * [QUANTITY] as Sales
FROM [lib://sql like/DEMO_ORDER_ITEMS.csv]
(txt, codepage is 28591, embedded labels, delimiter is ‘,’, msq);
First preceding load after initial table load will perform GROUP BY.
The next preceding load will use WHERE clause to mimic HAVING BY clause.
-Shilpan
Let us help you take your Qlik skills to the next level!
