The information in orders and invoices is typically stored at the header or line level in the database. However, when we display the sales value for a particular order on the UI, it is sometimes desired that all the products for an order are displayed in a single cell rather than on a separate line. The
Concat
function is helpful in such a case.
For this recipe we will make use of an inline data load which gives sales information for orders. Load the following order line information in Qlik Sense:
Orders: LOAD * INLINE [ OrderID,Product, ProductID, Sales 101,Footwear, F21Lon, 120000 101,Tyres, T21Man, 150000 101,Mountain Bikes, MB32Lon, 195000 102,Road Bikes, RB12Bir, 225000 102,Chains, F21Lon, 140000 103,lubricant, T21Man, 56869 103,Mountain Bikes, MB32Lon, 195000 104,Road Bikes, RB12Bir, 65233 ]; LEFT JOIN LOAD OrderID, CONCAT(Product,',') as Products Resident Orders GROUP BY OrderID;
OrderID
as the first dimension.Products
as the second dimension.Sum(Sales)
as the measure. Label it Sales
.The CONCAT
function in the script is used to string together multiple product values in one single string separated by a specified delimiter. The CONCAT
function is an aggregation function and would require a Group By
clause after the from
statement.
The CONCAT
function can also be used in the frontend instead of the script. In this case, we will have to create a calculated dimension, as follows:
=AGGR(Concat(DISTINCT Product,','),OrderID)
Name it as Products
. As mentioned earlier, being an aggregation function, CONCAT
requires an AGGR
that is a substitute of Group By
used in the script.
The Chapter 5, Useful Functions chapter discusses some cool utilization of functions within Qlik Sense.