Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Understanding Join, Keep and Concatenate

QVScriptor_Full_Small.png

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.

Table1

Table2
Key A
Key C
1 A1
1 C1
2 A2
2 C2
3 A3





4 C4

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)
Key A C
Key A C
1 A1 C1
1
A1 C1
2 A2
C2
2 A2 C2
3 A3 -
3 A3-
4 - C4




RIGHT JOIN (Table1)
INNER JOIN (Table1)
Key A C
Key A C
1 A1 C1
1 A1 C1
2 A2 C2

A2 C2







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


Table1

Table2
KeyA
KeyC
1A1
1C1
2A2
2C2
3A3
3-





RIGHT KEEP


Table1

Table2
KeyA
KeyC
1A1
1C1
2A2
2C2





4-
4C4

INNER KEEP


Table1

Table2
KeyA
KeyC
1A1
1C1
2A2
2C2










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;

Key A C
1 A1
1
C1
2 A2
2
C2
3 A3
4
C4

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

48 Replies
Highlighted
Contributor
Contributor

Hi John,

Many thanks for looking into this.

Basically I have a system table with columns (A,B,C,D) in the table 1 (meta data) with values in rows. Now I want to add some more data like alpha, Bravo to the same fields (A,B,C,D) but can't seem to find an exact way to do it.

Any help will be much appreciated.

Regards,

Mohammad.

Highlighted
Creator
Creator

Great overview.  Whenever I need to explain the Join, Keep, and Concatenate commands to anyone who is learning QlikView or Qlik Sense scripting, I always showcase this post to highlight the different ways to transform tables.  Thanks!

Highlighted
Not applicable

Qlikview ---> CONCATENATE

SQL ---> UNION

ACL ---> APPEND

Same thing; Different syntax

Btw, great explanations John!

Highlighted
Not applicable

Very good one, and indeed helpful.

Highlighted
Not applicable

One point of clarification, while the Left Keep and Right Keep would show all the remaining "Key" index values, 1,2,3 or 1,2,4 respectively, in table boxes, the actual data is not as pictured above.  In the left keep, Table2 will not have a value of 3 in the Key field, and in the right keep, Table1 will not have a value of 4 in the Key field.

To see this, load in the data, go to the Table Viewer, and then (in the case of the Left Keep) Preview data and you will see there is no value of 3 in this table. (as pictured below)

The values will show up in the front within a Table object due to the tables being linked on the Key field and the value of 3 still existing in Table1 (in the Left Keep case).  I assume this is what is meant in the post, but pictorially above it doesn't quite send this message.

Thanks.

Highlighted
Not applicable

This sounds like if you wanted to concatenate table2 under table1, not joining.

Highlighted
Contributor III
Contributor III

Nicely explained,

Highlighted
Creator III
Creator III

nicely explained

Highlighted
Specialist
Specialist

Absolutely one of the best explanations I've ever read. I've always come back to this anytime I need to explain the same concepts to someone or just to refresh my mind.

Best regards,

Andrés

Highlighted
Not applicable

Thanks a lot.