Download Set Analysis Primer

x

CONCAT Function

CONCAT function is a lesser-known yet a powerful aggregation function.

Since it is an aggregation function, you can use it in a Set Analysis expression. That opens up a world of possibilities for creative minds.

In this blog post, I want to present a few good use cases to leverage the CONCAT function’s power.

First, we will use the CONCAT function to include or exclude field values by triggering an event using a button.

In this used case, we will allow users to include or exclude the USA in various visualizations.

Next, we will use the CONCAT function to disregard user selections on all but few fields.

For example, we will allow user selections on the Product name, Country, and Year fields and ignore user selections on all other fields.

Back to Basics

Let’s start with the basics

CONCAT is a script and chart aggregation function.

Concat ([ distinct ] string [, delimiter [, sort-weight]])

It is used to combine string values. The script function returns the aggregated string concatenation of all expression values iterated over several records defined by a group by clause.

With those basics under our belt, let’s review a few used cases.

Include/Exclude Field Values

What if users want a button to toggle selections on a country field to include or exclude the USA?

Let’s create an expression to exclude ‘USA’ field value first.

concat({<[Customers.Country] -= {‘USA’}>}distinct [Customers.Country], ‘;’)

The expression to include all values is straight forward.

concat(distinct [Customers.Country], ‘;’)

 Now, we can toggle values using PICK/MATCH functions

pick(match(‘$(_Country)’, ‘USA’,’All’), concat({<[Customers.Country] -= {‘USA’}>}distinct [Customers.Country], ‘;’), concat(distinct [Customers.Country], ‘;’))

With bit of a creative juice, you can use this expression in an action to toggle values.

Notice that I am using the button chart object with a ‘Select values in a field‘ action along with a _Country variable to toggle field value and button title and icon dynamically.

You can also store each expression in a variable and use dollar-sign expansion to toggle select/exclude the USA from the country field.

 

Toggle Field Selections

What if we want to ignore user selections on all fields within the data model except few fields?

We can do that with ease; since CONCAT is an aggregation function, let’s use the power of Set Analysis to add modifiers or filters.

The Slope graph expression to accomplish our goal will have CONCAT function as a modifier along with a Set expression to ignore user selections on all fields except Customer Country, Product Name and Year fields.

sum({=$(=max(Year)-1) <=$(=max(Year))”}, [$(=concat({<$Field -= {‘Year’, ‘Customers.Country’, ‘ProductName’}>}distinct $Field, ‘]=,[‘) & ‘]=’)
>} Quantity * UnitPrice)

These are just a few examples of possibilities with the powerful CONCAT function.

The world is your oyster!

Let us help you take your Qlik skills to the next level!

-Shilpan

If you enjoyed this article, get email updates (it’s free).

* indicates required
About the author

Leave a Reply