topic Re: Understanding Join, Keep and Concatenate in QlikView

Understanding Join, Keep and Concatenate

Thu, 17 Nov 2011 15:18:22 GMT

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

RIGHT KEEP
Table1		Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2

4	-	4	C4

INNER KEEP
Table1		Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2

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

Understanding Join, Keep and Concatenate

Mon, 23 Jan 2012 19:09:45 GMT

Excelente, muy bien explicado, tenía dudas de esto; pero, con tu explicación quedé satisfecho.

Gracias.

Re: Understanding Join, Keep and Concatenate

Mon, 11 Feb 2013 22:55:47 GMT

Great overview. Thanks!

Re: Understanding Join, Keep and Concatenate

datanibbler — Wed, 08 May 2013 15:08:02 GMT

Hi,

this is a good overview - but there is one important issue it doesn't address:

The example for the LEFT JOIN - right now, I am only interested in that particular one and I have only looked at that - is a simple one: There is a unique key in table2 and so the LEFT JOIN works as you would expect, the number of records in the result matches the nr. of records in the original table1.

If, however, the key used in table2 is not unique, then the LEFT JOIN will, as I understand it, retain all those records from table2 that match the key in table1, so the result will have more records than the original table1.

Depending on the business application, this might be correct - it can be compared to a WHERE EXISTS clause and if the multiple records add a required detail level, that is all right, but if what you want is an additional info that should be there only once for every key_in_table1, then a non-unique key in your table2 represents an issue.

Re: Understanding Join, Keep and Concatenate

Thu, 30 May 2013 09:48:17 GMT

Very good explanation..It helps a lot ..Thank you.

Re: Understanding Join, Keep and Concatenate

Thu, 30 May 2013 09:48:41 GMT

Very good explanation..It helps a lot ..Thank you.

Re: Understanding Join, Keep and Concatenate

Thu, 30 May 2013 09:49:45 GMT

Very good explanation..It helps a lot ..Thank you.

Re: Understanding Join, Keep and Concatenate

Thu, 30 May 2013 10:02:37 GMT

Very good explanation..It helps a lot ..Thank you.

Re: Understanding Join, Keep and Concatenate

gainkarthi — Wed, 05 Jun 2013 07:48:16 GMT

Good one..

Re: Understanding Join, Keep and Concatenate

techvarun — Wed, 10 Jul 2013 07:00:27 GMT

Awesome post. Bookmarked

Re: Understanding Join, Keep and Concatenate

montubhardwaj — Wed, 24 Jul 2013 17:17:21 GMT

Good one. Thanks.

Re: Understanding Join, Keep and Concatenate

Anonymous — Thu, 25 Jul 2013 08:33:53 GMT

Thank you very much.
Would it be possible for you to add the respective statements that are used?

I am trying to get familiarised with the statement nomenclature.

Re: Understanding Join, Keep and Concatenate

sanketkhunte — Tue, 13 Aug 2013 11:47:34 GMT

Hi Friends,

Thank you for your Time.

Here I would like to ask one question regarding JOIN in QlikView.

Let say I have 2 facts : Fact_One and Fact_Two

In Fact_One there are few fields like : SegmentID1,SegmentID2,SegmentID3,SegmentID4.

and

in Fact_Two there is only one field for this mapping field is SEG_ID

Now My requirement is that How Can i join these two facts in Qlik View data model which satisfy :

if Control not find value in SegmentID1 of Fact_One table then it will goes to SegmentID2 and if control not finds match then this will go to SegmentID3 and all WITH SEG_ID

Your answer will appriciate me.

Regards

Sanket Khunte

Re: Understanding Join, Keep and Concatenate

Wed, 04 Sep 2013 19:18:50 GMT

You cam make 4 subsequents loads

1) Fact_One.SegmentID1 INNERJOIN Fact_Two.SEG_ID

2) Fact_One.SegmentID2 INNERJOIN Fact_Two.SEG_ID

3) Fact_One.SegmentID3 INNERJOIN Fact_Two.SEG_ID

4) Fact_One.SegmentID4 INNERJOIN Fact_Two.SEG_ID

Hope this help

Re: Understanding Join, Keep and Concatenate

vikasmahajan — Thu, 05 Sep 2013 03:46:20 GMT

Informative...

vikas

Re: Understanding Join, Keep and Concatenate

sanketkhunte — Thu, 10 Oct 2013 11:29:43 GMT

Thank you John.

Re: Understanding Join, Keep and Concatenate

Fri, 13 Dec 2013 08:09:10 GMT

Good explaination.

I have one more question. Can I have a Left Join & Left Keep in the same Load statement.

Eg.

Left Keep (ABC)

Left Join (XYZ)

Load

E, F, G

From OPQ.QVD(qvd);

Re: Understanding Join, Keep and Concatenate

Fri, 13 Dec 2013 08:43:41 GMT

Vinod,

I am not sure that syntax that you mention would work.

But yes, disparate Join/keep statement followed with respective Load statement would work.

Regards,

Re: Understanding Join, Keep and Concatenate

ashwanin — Fri, 13 Dec 2013 09:00:28 GMT

Nice .One of the best ppt in explanation.

Re: Understanding Join, Keep and Concatenate

Thu, 27 Feb 2014 08:07:45 GMT

thanks, i have seen many papers on joins .. this one is so far the best papers on joins ...