topic Re: WHERE EXISTS syntax in QlikView

WHERE EXISTS syntax

Thu, 17 Sep 2015 16:13:35 GMT

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

Re: WHERE EXISTS syntax

sunny_talwar — Thu, 17 Sep 2015 16:33:55 GMT

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;

Re: WHERE EXISTS syntax

Thu, 17 Sep 2015 17:21:39 GMT

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!

Re: WHERE EXISTS syntax

hic — Thu, 17 Sep 2015 17:22:17 GMT

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

Re: WHERE EXISTS syntax

Thu, 17 Sep 2015 17:34:19 GMT

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

Re: WHERE EXISTS syntax

hic — Thu, 17 Sep 2015 17:44:25 GMT

Then try a Left Join.

Re: WHERE EXISTS syntax

ramoncova06 — Thu, 17 Sep 2015 18:13:20 GMT

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

Re: WHERE EXISTS syntax

Thu, 17 Sep 2015 18:25:48 GMT

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

Re: WHERE EXISTS syntax

hic — Thu, 17 Sep 2015 18:27:43 GMT

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

Re: WHERE EXISTS syntax

Thu, 17 Sep 2015 18:29:59 GMT

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.

Re: WHERE EXISTS syntax

ramoncova06 — Thu, 17 Sep 2015 18:30:56 GMT

Sunny already replied back to the original question, if you want to use a where exists, you first have to use the field that already exists and then use the new field

Re: WHERE EXISTS syntax

Thu, 17 Sep 2015 18:31:12 GMT

Sorry, Henric, my mail cut off your reply at "You don't need it..." and I didn't see the rest of your comment. Apologies for the above post. Thanks for the answer.

Re: WHERE EXISTS syntax

sangeeth8787 — Wed, 02 Jan 2019 17:38:21 GMT

Can i use where exist condition instead of where clause.

I have situation, one of the previous developer created load script as

Load *

from

x.qvd(qvd)

where color = 'red' and year = 2018.

Because of two where conditions, the load is not optimized, how can we create the above load script with 2 conditions as optimized load. I have been searching in community but I haven't got much luck, could you please provide some hint on this scenario. Many Thanks in Advance