Concatenation Operation in QlikView Application: Part 1

This article describes concatenation operations in QlikView.

This article describes what the Concatenate Operator is in QlikView through SQL database.

Introduction

Concatenation is a process of combining two strings into one longer string. It is the most common operation performed with strings. Basically it combines two strings in one string. Concatenation is the table operator. It is similar to a Union table operator in ANSI SQL.

Example

First string: C#
Second string: Corner

The concatenation does an operation and adds these two strings to create "C#Corner".

Concatenation in QlikView

In QlikView, if two tables are concatenated with various sets of fields, then the concatenation of the two tables will be performed with the concatenation prefix.  CONCATENATE prefix adds rows to a previously loaded table.

The Syntax is:

concatenate[ (tablename ) ] ( loadstatement | selectstatement )

Use the following procedure to create a sample of concatenation in QlikView.

Step 1: Open the QlikView application

In the first step you need to open the QlikView application then go to File -> New then this window will be opened.

After clicking New option

Step 2: Open Edit Script

The second step is to open the edit script window from File -> Edit Script.

window of edit script

Then this window will be opened.

window

Step 3: Select Database

The third step is to select the database from the data tab and click on the Connect Button.

select database

Step 4: Data link property window


In this window we select SQL Server and click on the Next Button.

data link

Step 5: Connection tab

In the connection tab enter the server name and user name and password and select the database from your server and click on Test Connection.

test connection

Then you find that the connection has succeeded; click on the OK button.

Confirm box

Then click on the OK button.

ok box

Step 6: SQL Server login

Then the SQL Server login is required so enter a valid login id and password and click on the OK button.

SQL server

Then this window will be opened.

edit1

Step 7: Select database

Now the next step is to select the database from the table.

select statement

Then this box will be opened and select the table from your database.

database

Step 8: Write code and reload it

Now in the edit script, write this code and after this reload it.

load

Step 9: Save QlikView file

After reloading the edit script, the next step is to save the QlikView file and click on the Save button.

save

Step 10: Script execution progress

Then the script execution progress window will be opened and requires a login Id and password and click on the OK button.

Script execution

Step 11: Sheet property Window

After this process, the sheet property window will be opened. Now here you will see table fields. If you want to show these table fields as a list box then click on the add button otherwise click on the OK button.

sheet

Step 12: Select a table

The next step is to select a table for displaying a table field in a table. Then go to Layout -> New Sheet Object -> Table Box.

table

Then this window will be opened. Here add all fields to the table then click on the Ok button.

data

Now you will see this output.

output

Step 13: Perform Concatenate operation

The next step is to open the edit script again and do a concatenate operation in the edit script and reload it.

reload2

  1. Employee:  
  2. SQL SELECT Gender,Id  
  3. FROM Employee.dbo.Employee  
  4. WHERE Gender='Male';  
  5.   
  6. Concatenate(Employee)  
  7.   
  8. SQL SELECT Gender,Name  
  9. FROM Employee.dbo.Employee  
  10. WHERE Gender='Female';  
  11.    
Step 14: Script execution progress

Then, again the script execution progress window will be opened and requires a login Id and password and click on the OK button.

Script execution

Step 15: Sheet property Window

After this process, the sheet property window will be opened. Now here you will see 3 new table fields that we select. If you want to show these table fields as a list box then click on the add button otherwise click on the OK button.

sheet1

Now you will see the previous table converted into this table after doing a concatenate operation in the table.

output1

Summary

This article described how to do concatenation operations in QlikView. This article is very helpful when you are working with table data.

I hope all of you enjoyed this article.