11 Replies Latest reply: Sep 17, 2015 2:31 PM by Kevin Bertsch RSS

    WHERE EXISTS syntax

    Kevin Bertsch

      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

          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
              Kevin Bertsch

              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
              Henric Cronström

              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
                  Kevin Bertsch

                  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
                  Ramon Covarrubias

                  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
                    Kevin Bertsch

                    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