topic Re: LEFT JOIN IN QLIKVIEW ? in QlikView

LEFT JOIN IN QLIKVIEW ?

Mon, 04 Feb 2013 16:50:01 GMT

Hi,

I am trying to join two tables in Qlikview but require all the data in Table 1 and only related data in Table 2. Therefore in SQL this would require a straight forward left join however I can't seem to get this working in QV.

Qualify*; 
UnQualify CUST_ID ;
DATA:
LOAD "CUST_ID", 
     "123",
     "456" ,
SQL SELECT *
FROM "TABLE_1";
Qualify*; 
UnQualify CUST_ID ;
LEFT JOIN (DATA)
SALES:
LOAD "CUST_ID", 
     "ABC",
     "EFG", 
SQL SELECT *
FROM "TABLE_2";

I require all the values in CUST_ID in TABLE 1 - e.g. in the application if I run a simple COUNT(CUST_ID) it should show the total number of values in TABLE1 for CUST_ID. However unfortunately this is not happening! Where am I going wrong?

Re: LEFT JOIN IN QLIKVIEW ?

swuehl — Mon, 04 Feb 2013 17:05:41 GMT

What do you see instead? A higher or lower number of records?

Just an idea: Is CUST_ID a key field in your application?

You shouldn't count() a key field in QV.

So if it is, try counting another field with 100% information density (edit: in that table) or check the number of rows in table viewer.

Re: LEFT JOIN IN QLIKVIEW ?

Mon, 04 Feb 2013 17:13:19 GMT

In your script there are some syntax errors, and I don't know if they are due to transcription or not. Try this script, it should be work:

Qualify *; UnQualify CUST_ID ; DATA: LOAD "CUST_ID", "123", "456"; SQL SELECT * FROM TABLE_1; Qualify *; UnQualify CUST_ID ; LEFT JOIN (DATA) LOAD "CUST_ID", "ABC", "EFG"; SQL SELECT * FROM TABLE_2;

Re: LEFT JOIN IN QLIKVIEW ?

Anonymous — Mon, 04 Feb 2013 18:09:38 GMT

Hi,

Please check the application,it'sworking correctly,Can you do left join after storing a qvd.

i hope that's will be work.

Regards

Re: LEFT JOIN IN QLIKVIEW ?

Tue, 05 Feb 2013 08:30:38 GMT

Hi Swuehl, I am getting less number of records.
However I am counting on a key field. But why would this matter? I need to count on this field as its the only accurate way to count the number of customers (each customer must have an id)

Juan - thanks, this was just a typo. The syntax is correct in the application

Any other idea?

Re: LEFT JOIN IN QLIKVIEW ?

Tue, 05 Feb 2013 08:31:39 GMT

Juan - thanks, this was just a typo. The syntax is correct in the application

Any other idea?

Re: LEFT JOIN IN QLIKVIEW ?

Tue, 05 Feb 2013 09:33:42 GMT

With that syntax, the script works perfect. Maybe you are not counting correctly the records, or your tables/views have other records than expected.

Re: LEFT JOIN IN QLIKVIEW ?

Tue, 05 Feb 2013 10:04:22 GMT

Thanks Juan, maybe I don't understand how joins exactly work in QV...what I'm trying to do is count the number of customers in TABLE 2

Example, if I have 100 customers in TABLE 2 but only 50 of these customers exist in TABLE 1. How do I count the total number of customers in TABLE 2 i.e. 100? I am using COUNT(CUST_ID) but it seems I'm only getting the number of customers that exist in both tables i.e. 50. Is it possible to count on an Index?

Re: LEFT JOIN IN QLIKVIEW ?

swuehl — Tue, 05 Feb 2013 10:11:40 GMT

As I said, you shouldn't count() on a key field. The key field is part of multiple tables, and since you don't define the table you want to count the field's values in, QV doesn't know how to handle this. The answer is not really defined, e.g. depending on load order of your tables.

You can do a count(distinct KEYFIELD), but this will do a distinct count of course.

Or count a field in the table with 100% information density (i.e. there is a value for each record in that table). For example, if each Customer must have a last name entered into the system, count the [Last Name] field.

If you could post a small sample app or a more complete script snippet, we can help you with the concrete syntax.

Re: LEFT JOIN IN QLIKVIEW ?

Fri, 09 Jan 2015 15:09:30 GMT

Good work, thanks for the help !!!

Re: LEFT JOIN IN QLIKVIEW ?

Mon, 12 Jan 2015 11:38:22 GMT

hello,

if i have more then 4 tables but i need to create "LEFT JOIN" all of 3 tables into 1 table then what should i use?

i want to create left join from Dish_Index table

kindly help

Re: LEFT JOIN IN QLIKVIEW ?

Mon, 12 Jan 2015 12:04:20 GMT

plz help

Re: LEFT JOIN IN QLIKVIEW ?

Sat, 17 Jan 2015 20:47:00 GMT

Hi,

Does this help:

Dish_Index:

LOAD

FileName_ID,

Field2,

//add more fields

FROM Dish_Index;

//Left Join - the field names in the two tables being joined must match

// Field1=Field1 (left join) for all tables joined with Dish_index

//first table: Dish_Index LEFT JOIN ON Dish_phrase

LEFT JOIN (Dish_Index)

LOAD

FileName_ID,

Field3,

//add more fields …

FROM Dish_phrase;

//Second table: Dish_Index LEFT JOIN ON Dish_custom_topics

LEFT JOIN (Dish_Index)

LOAD

FileName_ID,

Field4,

//add more fields …

FROM Dish_custom_topics;

Re: LEFT JOIN IN QLIKVIEW ?

Tue, 03 Mar 2015 03:19:55 GMT

if you want to join all table in one table i advise use concatenate instead of left join or right join because it will affect on your application performance,it will slow in reloading time. also in addition left join or right join are used based on the requirement, but if you want to join table in one table only used concatenate.

Re: LEFT JOIN IN QLIKVIEW ?

Ibobob70 — Tue, 23 Apr 2019 11:40:30 GMT

I have the same problem but higher number of records than my original table :). and I do not count key-field.