topic EXISTS - can I specify a table_name? in QlikView

EXISTS - can I specify a table_name?

datanibbler — Mon, 26 Jan 2026 18:19:17 GMT

Hi,

I have a rather complicated tool for historizing personell_data - comparing every day if any emps have left and if any emps have started.

In this, I use the >WHERE NOT EXISTS()< several times.

I just wonder - there are a few RESIDENT LOADs, so there might be confusion with the field_names. I think it works, but I like to keep everything as explicit as possible, just makes everything easier to maintain.

Can I, in an EXISTS()_clause, specify a table_name along with the field_name so that it's quite clear which field is being referred to?

Thanks a lot!

Best regards,

DataNibbler

Re: EXISTS - can I specify a table_name?

tresB — Tue, 26 Aug 2014 07:42:12 GMT

You can like(with a dot):

Where Exists(tablename.Field1, field2)

Re: EXISTS - can I specify a table_name?

datanibbler — Tue, 26 Aug 2014 08:01:42 GMT

Excellent!

Thanks! I'll do that right away.

You know, I am pretty sure it works - in fact, it was working, only a while ago I forgot an EXIT command in the code and so it "ran on empty"... but it's like driving a car: Seeing another car and thinking "I need to break" is not enough, you have to let the other driver know you've seen him and you're going to break.

I'm going to insert that into the commands so anyone looking at the code lateron will get an idea of what's being done.

Re: Re: EXISTS - can I specify a table_name?

datanibbler — Wed, 27 Aug 2014 06:27:38 GMT

Hi Tresesco,

I have a wee problem now: In my historization_table (that is the data from yesterday), there are 888 lines - 888 emps, that is.

In my "new_data" (data that I drew from the database today) there are 887 - so 1 employee must have left the company since yesterday.

I then compare those two tables in both directions to find out about both

- employees who are in the today_table, but not in the history_table (new starters)

- employees who are in the history_table, but not in the today_table (leavers)

<=> This doesn't work. According to the log, that 1 employee is not considered.

As always with personell_data, providing sample_data is a bit tricky, but I can provide my code - it used to work until mid-July, so I guess it's something about that WHERE-thing that you told me yesterday - or rather about how I used it

I will attach the code so as not to let this post get too big and cumbersome to read.

Please have a look and see where the error is - as I said, it used to work, so it is correct in principal. It must be some little thing.

Thanks a lot!

Best regards,

DataNibbler

P.S.: I hope you're ok with German, the comments are all in German as we're a German company and way too many Germans don't quite understand English ...

P.P.S.: It's definitely that tablename-thingy in the EXISTS() clause - I removed it and it works just as expected.

Re: Re: Re: EXISTS - can I specify a table_name?

tresB — Wed, 27 Aug 2014 07:46:25 GMT

Hi DataNibbler,

I am unfortunately not okay with German(language only. ). However, that might not be a big issue. I preferred to explore with a sample data. Thank you that your issue drove me to a new finding. I tried like:

Old:
Load ID Inline [
ID
1
2
3
4];
NoConcatenate
New:
Load ID Inline [
ID
2
3
4
5
];
NoConcatenate
Added:
load
ID as AddedID
Resident New where not Exists(New.ID, ID);

NoConcatenate
Deleted:
Load
ID as DeletedID
Resident Old Where not Exists (New.ID, ID);

And that resulted:

Alternative(Renaming the field) Tried:
Old:
Load ID Inline [
ID
1
2
3
4];
NoConcatenate
New:
Load ID as IDNew Inline [
ID
2
3
4
5
];
NoConcatenate
Added:
load
IDNew as AddedID
Resident New where not Exists(ID, IDNew);

NoConcatenate
Deleted:
Load
ID as DeletedID
Resident Old Where not Exists (IDNew, ID);

To conclude, my findinds(as long as anybody else finds otherwise) are:

Exists() - actually doesn't throw any error if the field <first parameter> is a vague one. [That is why there was no error with <tablename>.<field> convention; actually it doesn't take table reference. However, it may sometimes confuse one when it works preceded by a Qualify key, because that actually generates field name with table reference.]

Hence, my alternative way of renaming the fields is the one I recommend. Note, you can keep the original fields in additiona to these new names to maintain the table relationships and delete (by using 'Drop Fields') unwanted fields at the end.

Re: EXISTS - can I specify a table_name?

datanibbler — Wed, 27 Aug 2014 07:51:23 GMT

Thanks a lot!

I already have a comment for each of the WHERE()_clauses, actually that should be sufficient if one just reads it 😉

I will try renaming the fields to make it quite clear what is meant.

Best regards,

DataNibbler