Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
arethaking
Contributor II

Difference between concatenation,joins and keep functions in qlikview

Hi All,

Please help with examples

Thanks & Regards

Aretha

1 Solution

Accepted Solutions
Arjunarao
Honored Contributor II

Re: Difference between concatenation,joins and keep functions in qlikview

Hi,

Please go through this. It is very clearly explained.

Understanding Join, Keep and Concatenate

We have implemented the QlikView Join, Keep and Concatenate in QvScriptor, a new revolutionary tool, able to generate QlikView script from visual schema.

We have written this small doc for the help file and we thing interesting to share it with the community as we found only partial explanations about the methods.

The QlikView script functions JOIN, KEEP and CONCATENATE can sometimes be used to solve the same problem, but there are important differences that should be understood.

Examine the sample tables below. Note that they share one common field name, "Key".

Also note that Table1 has a Key value "3" that is not present in Table2 and that Table2 has a key 4 that is not present in Table1.

Table1Table2
KeyAKeyC
1A11C1
2A22C2
3A3
4C4

Type of Joins:

JOIN will combine rows where the Key value matches.

  • The keyword OUTER will also retain rows that do not match rows in the other table.
  • The keyword LEFT will retain rows from the left table but only matching rows from the right table
  • The keyword RIGHT will retain rows from the right table but only matching rows from the left table
  • The keyword INNER will retain rows matching the left table and right table

Joins

Here's what the merged table will look like after the different join methods.

Note that QlikView merge the two tables after a Join.

Note: The explicit join keyword (= Outer Join) in QlikView script language performs a full join of the two tables. The result is one table. In many cases such joins will result in very large tables. One of the main features of QlikView is its ability to make associations between tables instead of joining them, which greatly reduces memory usage, increases processing speed and offers enormous flexibility. Explicit joins should therefore be generally avoided in QlikView scripts. The keep functionality was designed to reduce the number of cases where you need to use explicit joins

OUTER JOIN (Table1)LEFT JOIN (Table1)
KeyACKeyAC
1A1C11
A1C1
2A2
C22A2C2
3A3-3A3-
4-C4

RIGHT JOIN (Table1)INNER JOIN (Table1)
KeyACKeyAC
1A1C11A1C1
2A2C2
A2C2
4-C4

Keep

The keep prefix between two load or select statements has the effect of reducing one or both of the two tables before they are stored in QlikView, based on the intersection of table data. The keep keyword must always be preceded by one of the prefixes inner,left or right. The selection of records from the tables is made in the same way as in a corresponding join.

However, the two tables are not joined and will be stored in QlikView as two separately named tables.

LEFT KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2
3A33-

RIGHT KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2
4-4C4

INNER KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2

Concatenate

Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;

KeyAC
1A1
1C1
2A2
2C2
3A3
4C4

Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.

QsGen Team

4 Replies
Highlighted
arvind1494
Valued Contributor

Re: Difference between concatenation,joins and keep functions in qlikview

Concatenation:

It is similar to union in SQL

When u concate two table then records of both tables get murged into one table

NOTE: In concatenation duplicate records will not remove.

Joins:

It is usefull for joining two tables

left,right,inner,outer joins are available in qlik

Keep:

Keep is similar to the join except that keep store both the tables separately in data model.

sibin_jacob
Contributor III

Re: Difference between concatenation,joins and keep functions in qlikview

Please go through the below link

It is having details explanation with examples.

https://community.qlik.com/message/703842?&_ga=2.56573547.1558869334.1533710704-1319351581.153362034...

ChennaiahNallani
Contributor III

Re: Difference between concatenation,joins and keep functions in qlikview

Arjunarao
Honored Contributor II

Re: Difference between concatenation,joins and keep functions in qlikview

Hi,

Please go through this. It is very clearly explained.

Understanding Join, Keep and Concatenate

We have implemented the QlikView Join, Keep and Concatenate in QvScriptor, a new revolutionary tool, able to generate QlikView script from visual schema.

We have written this small doc for the help file and we thing interesting to share it with the community as we found only partial explanations about the methods.

The QlikView script functions JOIN, KEEP and CONCATENATE can sometimes be used to solve the same problem, but there are important differences that should be understood.

Examine the sample tables below. Note that they share one common field name, "Key".

Also note that Table1 has a Key value "3" that is not present in Table2 and that Table2 has a key 4 that is not present in Table1.

Table1Table2
KeyAKeyC
1A11C1
2A22C2
3A3
4C4

Type of Joins:

JOIN will combine rows where the Key value matches.

  • The keyword OUTER will also retain rows that do not match rows in the other table.
  • The keyword LEFT will retain rows from the left table but only matching rows from the right table
  • The keyword RIGHT will retain rows from the right table but only matching rows from the left table
  • The keyword INNER will retain rows matching the left table and right table

Joins

Here's what the merged table will look like after the different join methods.

Note that QlikView merge the two tables after a Join.

Note: The explicit join keyword (= Outer Join) in QlikView script language performs a full join of the two tables. The result is one table. In many cases such joins will result in very large tables. One of the main features of QlikView is its ability to make associations between tables instead of joining them, which greatly reduces memory usage, increases processing speed and offers enormous flexibility. Explicit joins should therefore be generally avoided in QlikView scripts. The keep functionality was designed to reduce the number of cases where you need to use explicit joins

OUTER JOIN (Table1)LEFT JOIN (Table1)
KeyACKeyAC
1A1C11
A1C1
2A2
C22A2C2
3A3-3A3-
4-C4

RIGHT JOIN (Table1)INNER JOIN (Table1)
KeyACKeyAC
1A1C11A1C1
2A2C2
A2C2
4-C4

Keep

The keep prefix between two load or select statements has the effect of reducing one or both of the two tables before they are stored in QlikView, based on the intersection of table data. The keep keyword must always be preceded by one of the prefixes inner,left or right. The selection of records from the tables is made in the same way as in a corresponding join.

However, the two tables are not joined and will be stored in QlikView as two separately named tables.

LEFT KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2
3A33-

RIGHT KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2
4-4C4

INNER KEEP
Table1Table2
KeyAKeyC
1A11C1
2A22C2

Concatenate

Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;

KeyAC
1A1
1C1
2A2
2C2
3A3
4C4

Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.

QsGen Team