Concat() in QlikView

I'm happy to write a series of blog about useful String functions in QlikView in Detail. As a Start we will have a look at Concat() function and how this can be used in Real Life Scenarios. Before that if you didn't get a chance to have a look on my previous Post on one of the QlikView function Dual(), You may have a look at here.

When i started my Career with QlikView, I was not much aware about Concat() function but when i got introduced to it, i have started using it very frequently in one or the other way. This function can be used in both Chart as well as Backend Scripting. Output of the Concat() function is always String value which Concatenate all the value in the Expression over the Specified dimensions.

Syntax of the Concat() Function as follows.

concat ( [ distinct ] expression [, delimiter [, sort-weight]] )

Expression   -  Field or Expression to be concatenated.

Delimiter       -  Each value may be separated by the string found in delimiter

Sort-Weight  - The order of concatenation may be determined by sort-weight.Sort-weight should return a numeric value where the lowest value will render the item to be sorted first.

Let's see with the Example, how this Concat() function work, Im taking a simple data with inline table to Explain this.

I have Added  Inline table  and reloaded the Dashboard. Below are the three expressions by adding Parameters.

1. Concat(TestConCate)    - Result of the Expression is ABCD

2. Concat(TestConCate,',') - Result of the Expression is Delimited by Comma  A,B,C,D

3. Concat(TestConCate,',',SortOrder)  -  Result of the Expression is Sorted by 3rd Parameter D,C,B,A

Let's have a look at some of the ways concat() function can be used in qlikView with few scenario`s

Scenario 1: Concatenate Multiple Rows into Single Row Based on the Specified Fields.

For Example your are working with a company who manufactures LAPTOP. Laptop which has been sold to end customer has some defect and it has been returned to your company from end customer. So to provide a replacement for the end customer, this defective laptop has to go for Many internal approvals. So in ERP tables, this may be stored as one laptop name with multiple records for each department approval.

Let's assume that our data in Database looks as below.

Let's take this data for our analysis. Laptop A Got into 5 different approvals but Laptop B has got into only 3 approvals. Now your user Wanted a report in qlikview which concatenates all the approvers records by Distinct laptop into single row. so Expected result from the above data is 2 records.

You can Achieve this Scenario by Multiple way  one way is using Previous() and Peek() functions but getting this is bit Difficult and time consuming with these functions. Easiest and very simple way would be using concat() function.  Just using the Field name with Delimiter and Group by function will Do the Magic. Sample Code Should look like the below one.

And  Output is

It is just simple as that to concatenate multiple rows into single row in QlikView :)

Scenario 2: Concat() in Set Analysis with Island Table.

For Example you are getting requirement from customer that you'll have to restrict data for a report (Not from the Complete data model)  using the data from Excel file which does not have any link to your existing data model. Yes, We will look for optimized and robust Solution get the work done and this can be achieved using many ways, like Set Analysis using Concat() function, or using applymap() function from backend by  creating Flag,  or Combination of Set analysis using WildMatch function at frontend chart.  Let's have a look on few of them now.

I`m going to take the same Work flow data what i have used above for this as well with additional Excel file which i have Received from user (in this case i'm loading the data using INLINE Table itself) as shown  below. Information inside Field IGNORE_FROM_REPORT has to be used to ignore it from report. We have Got AUDITOR and MANAGER information is available in this igone field, So user don't want to see the approval flow from Auditor and Manager in report.

Once i reloaded my Dashboard, I`ll have two Seperate table Without any link.

If you have island table in QlikView without any link to your Data Model, you would not be able to use this island table field to equate it to your datamodel field in Set Analysis. Below Expression would not work and Fails & gives you all the data.  So don't think to Equate the Field Directly in Set Analysis like DATAMODEL_FIELD = ISLAND_TABLE_FIELD which would always gives you wrong data untill unless you have correct link.  

Count ({<Approvers -= {IGNORE_FROM_REPORT}>} Approvers) // Always Produces Wrong Data

So in this case Concat() function comes in Handy With Set Analysis. To Equate the String field in Set Analysis i need my value to be single quoted and comma separated. So i'm going to form a expression using Concat() function which can be used inside the set analysis to get the expected result. I would need something like 'AUDITOR','MANAGER' to use it in set analysis,

so let me Start forming the Expression Step by Step. Concat() function with Delimiter as comma would give me result as AUDITOR,MANAGER

Concat(IGNORE_FROM_REPORT,',')

But i would need single Quote to be concatenated before and After the each value, so i`m going to use Chr(39) function to concatenate the Single Quote. 39 is ASCII value for Comma.

chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39)

Now i got  the Expected string out of the above Expression ('AUDITOR','MANAGER' ) which i can use inside Set analysis .So this Expression can be used in set analysis as show below directly or using the Match/WildMatch functions.

count({<Approvers = {$(=chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39))}>} Approvers)

count({<Approvers = {"=Wildmatch(Approvers,$(=chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39)))>0"}>} Approvers)

First Expression Column in above table is producing wrong result as we have used the ISLAND FIELD directly in our data model without any link. Second and Third expressions are  producing Expected result as we are treating the ISLAND field as String Value and Equating them back in our Data Model.

Hope this gives you a picture of how Concat() function can be used with different functions and Set Analysis.

Thanks for reading the post and Happy Qliking !!!