WHERE EXISTS syntax

[#1]

Qlik is a great product, but its documentation is sadly behind.

I am trying to add a field to an existing QVD. Following advice in the community, I used the following:

RefNote:

Load

  *

FROM GM1988AndOnwardNotes.qvd (qvd);

// The 'Records' field in the above QVD was aliased from "RECID" for backwards compatibility

// Now I want to add the REF field to the QVD, joining on the RECID (1:1 with existing QVD)

JOIN

LOAD

  Records,

  REF;

SQL Select

  RECID AS Records,

  REF

FROM

  CONTHIST; // this code taken (with obvious field name changes) from a community post

This actually works fine. However, where there 800k records in the QVD, the new load generated 1.1 million records, so I wanted to restrict the load to only records that already existed I tried adding a WHERE clause

WHERE EXISTS (Records). This got an error "wrong syntax near Records".

Did a little more digging, saw that I apparently need the field name from both the existing QVD and the

new table in the EXISTS, so I tried

WHERE EXISTS (Records, RECID), but that also got a syntax error, as did every possible combination

(e.g. (RECID, Records), (Records, RECID), (RECID,RECID) and (Records, Records).

What is the correct syntax for this?

thanks,

Kevin

[sunny_talwar]

Where are you doing this in the QlikView script or the SQL script?

JOIN

LOAD

  Records,

  REF

WHERE EXISTS (RECID, Records);

SQL Select

  RECID AS Records,

  REF

FROM

  CONTHIST;

[#3]

I don't know what you mean by "SQL Script" and "QV Script"; I have one script that mixes and matches QV and SQL statements.

If I'm reading you correctly, you are saying the WHERE clause needs to be before the SQL select, but it is precisely that SQL select that I'm trying to limit!

When the initial QVD was created, I only loaded fields from ContHist where there were "NOTES" fields. There are many records in ContHist that don't have a NOTES field, and I'm not concerned with them. However, II do want the REF field to be associated with the correct record, and that is the purpose of the RECID field (it is the primary key).

What I want is to add the REF field from ContHist to my existing QVD for any record that ALREADY exists in my QVD.

I will try your suggested syntax this afternoon, and see what happens. Still think the documentation could be better, though!

[hic]

You say that you want to "load to only records that already existed". But this is not well defined... The reason that you get more records is that you join two tables, and then some records will be duplicated. So you will have two records that have all fields identical, but have different values in REF. Which record should you discard?

If you always want the first one only, you shouldn't use Join. You should use Applymap(). See Don't join - use Applymap instead

HIC

[#5]

Sorry, Henric, but your assumption that there are two or more REF fields for any given record exist is false.

Each record in ContHist has a unique RECID, plus other fields, such as User, ActionCode, ResultCode, Date, Time, and REF. "Notes" are actually captured in another table, also linked on RECID, and I joined NOTES to ContHist when I created the initial QVD. , So, in my initial QVD, I have RECORDS (aliased from RECID), User, (other fields), and NOTES (1 note per RECID). Now I have been asked to add the REF column to the qvd..So,, e.g.

Record Numbers         

QVD       ContHist    REF

                1             Add Account

2              2             spoke to client

               3              Updated address

4             4              scheduled meeting

I only want to add the REF data from ContHist for RECID's 2 and 4.to my QVD data, and then store the new file into a

new QVD.

Hope this is clearer!

thanks,

Kevin

[hic]

Then try a Left Join.

[ramoncova06]

based on your reply to Sunny. having a hybrid of SQL and QVD is not as straight forward, and I would not recommended using it for key type of fields, so either you use different joins in the SQL sentence to filter the data out or you do a full extraction and you do a join as Henric Suggested

[#8]

I would be ever so happy to get a simple answer to my original question:

what is the right syntax for EXISTS in the WHERE clause?

If I'm joining two tables where the field is called "Key1" in the first table (Table1), but is called "Key2" in the second table, (Table2) is the syntax:

Load

     Key1,

     fields

from Table1;

JOIN

LOAD

     Key2

     Fields

FROM Table2

WHERE EXISTS ?? (Key1, Key2) or is it (Key2, Key1) or something else?

thanks,

Kevin

[hic · Accepted solution]

You don't need the Where exists. The Left Join will do exactly that.

But if you would use the Exists function, you could do it like this:

Load

     Key1,

     fields

from Table1;

JOIN

LOAD

     Key2 as Key1

     Fields

FROM Table2

WHERE EXISTS(Key1, Key2) ;

The first parameter should be a previously existing field, and the second a field or expression in the current load.

HIC

[#10]

Thanks, Henric, but there will be occasions when I need it, and i am asking what I think is a simple question that is NOT covered in the documentation. I would just like clarification on this one point.