Simple? The >WHERE EXISTS< function - Qlik Community

datanibbler · ‎2013-09-06

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

tresesco · ‎2013-09-06

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

datanibbler · ‎2013-09-06

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.

tresesco · ‎2013-09-06

Hi,

Right, it would avoid duplicate values for that field. Working would be like: If first value 'A' is loaded, and then comes 'B', the exists finds no 'B' loaded till then, so it goes ahead and load 'B'. Now for next iterations neither of 'A' or 'B' would be allowed to load, because they already exist.