where exists small script explanation

[joioan]

Hello all.

I need help understanding the following script:

Table1:

Load Key from source;

Table2:

Load * where exists(Key);

Load *, Key from ExcelFile;

I thought firstly  Load *, Key from ExcelFile; is evaluated, then Load * where exists(Key);   returns the result of Load *, Key from ExcelFile;   and that should be how Table2 is created.

but in this case,   Load * where exists(Key);    obviously (?)  evaluates also Table1.

Am I correct?

[steeefan]

where exists(Key) will look at all occurrences of Key, not just in one table. See the explanation in Qlik Sense help pages.

[Ahidhar]

where exists(key) will search and evaluate at all tables where 'key' exists that is why it is used with resident because then it will know which table it should evaluate .

[joioan]

Hello 🙂 So this why it's important to also denote a table along with Key field like WHERE EXISTS (Table2, Key)? This will load for table 2 the keys that exist in Table1?

[marcus_sommer]

exists() evaluates the field-values against the related system-table which makes it independent to the source. In this regard has the order of the load-statements an impact. That's neither bad nor particularly difficult in the usage else you just need to consider this behaviour in your development-measurements.

Beside moving certain load-statements up and down in your script and/or outsourcing them in a multi-tier data-architecture you may also use exists(ExistingField, FieldOrExpressionFromTheCurrentLoad) with a second-parameter and/or renaming fields (not mandatory once else also back and forth) and/or duplicating the fields with an extra name and deleting them afterwards. 

[Ahidhar]

where exists( field name from a previously loaded table , field from current loaded table)

Table2:

Load * , Key from Excelfile;

load * resident Table2 where exists(Key , Key);

[joioan]

Hi @Ahidhar let's see if i have understood this:

1) 

a) Let's say we have loaded tables before the table with where exists(key);   and also tables loaded after this table. The statement where exists(key) looks for all field values loaded in field key in ALL of the previous and latter tables? In all my script ever?

b) Now If I denote where exists(key, 'value') will do the same but for field value?

2) But, if in the same scenario the table with where exists(key) is a resident Load from a previous table it will look for existing key values and that's where it stops looking? Or it still checks all of the script?

Many thanks in advance, where exists() has me really confused.