LEFT JOIN IN QLIKVIEW ?

[#1]

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?

[swuehl]

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.

[#3]

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;

JG

[Anonymous]

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

[#5]

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?

[#6]

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

Any other idea?

[#7]

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

JG

[#8]

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?

[swuehl]

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.

[#10]

Good work, thanks for the help !!!