Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

EXISTS - can I specify a table_name?


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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

You can like(with a dot):

Where Exists(tablename.Field1, field2)             

View solution in original post

5 Replies
tresesco
MVP
MVP

You can like(with a dot):

Where Exists(tablename.Field1, field2)             

datanibbler
Champion
Champion
Author

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.

datanibbler
Champion
Champion
Author


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.

tresesco
MVP
MVP

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:

Screen Shot 08-27-14 at 12.46 PM.PNG.png

Screen Shot 08-27-14 at 12.54 PM.PNG.png

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); 

Screen Shot 08-27-14 at 12.52 PM.PNG.png

Screen Shot 08-27-14 at 12.53 PM.PNG.png

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.

datanibbler
Champion
Champion
Author

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