3 Replies Latest reply: Sep 6, 2013 6:18 AM by Tresesco B RSS

    Simple? The >WHERE EXISTS< function

    Friedrich Hofmann


      Hi,

       

      maybe I'm the only one seeing a problem here, but I think that in both the official QlikView manual and in the book "QlikView 11 for developers" (at least in the version I use), the WHERE EXISTS function, used to get rid of dimension_data (a.k.a. masterdata) without any matching fact_data, is not made entirely clear: I think I know it,  but I always have to think about it for a while, so in case my idea is correct, I guess I would make it a document:


      - The whole thing makes sense only using the KEYFIELDS you have in both tables.

      - The WHERE EXISTS fct. takes two parameters.

           - The first parameter specifies the field where to look for a value - that field has to be in THE OTHER table, the one that has 
              already been loaded into QlikView

           - The second parameter specifies the value to look for in this field - that has to be a field in THIS table and in case you are using
              any Alias_names in your LOAD statement (using >as<), you have to type here the ORIGINAL name of the field.

       

      Could anybody please just conform whether this is correct and correct me if it's wrong? I will then make this discussion a document so everyone else can look it up more easily.

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Simple? The >WHERE EXISTS< function
          Tresesco B


          Hello Hofmann,

           

          Here is my view:

          Friedrich Hofmann wrote:

           


          - The whole thing makes sense only using the KEYFIELDS you have in both tables.

                Not necessarily. You can even use this to exclude some values in one table based on their presence in other table/field, where the KEYFIELDS concept might not be an essential.

           

          - The WHERE EXISTS fct. takes two parameters.

              - The first parameter specifies the field where to look for a value - that field has to be in THE OTHER table, the one that has already been loaded into QlikView.

               Here too, I have to disagree, you can use:

          Load A, B, C, from Employees.csv where not exists (A);
          This is equivalent to performing a distinct load on field A. So here, there is no reference to other table.


             

              - The second parameter specifies the value to look for in this field - that has to be a field in THIS table and in case you are using
                  any Alias_names in your LOAD statement (using >as<), you have to type here the ORIGINAL name of the field.

           

          Could anybody please just conform whether this is correct and correct me if it's wrong? I will then make this discussion a document so everyone else can look it up more easily.

           

          Thanks a lot!

          Best regards,

           

          DataNibbler

            • Re: Simple? The >WHERE EXISTS< function
              Friedrich Hofmann

              Hi tresesco,

               

              I understand your point. So this function can be used for several purposes.

              I understand that in a scenario where you use this to EXCLUDE the values that are present in another table, you need not necessarily use the keyfield.

              Maybe I don't quite understand what you mean by "distinct load"? Do you mean you would use that method to avoid duplicates?

               

              P.S.: The whole thing is relevant for me right now because, in an app I had already built before and where I use this function in the script, I have now added data from another database (a mirror of the old one) and I had to build new compound_keys to have the same key_quality in all the tables - now I wonder whether I should use these new keyfields in those WHERE EXISTS clauses as I was using the keyfields before.