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


Hi,

I have been here some times before with similar issues and I remember that this function is in all cases not as easy as the integrated help_file suggests. I remember for sure that it cannot be written with just one parameter as the help suggests. I think it only works if one or b oth of the key_fields in the tables are unique.

=> Well, that >or< is my issue. The scenario is this:

- In table_A (which I load earlier) that key_field (which I want to base the EXISTS() clause on) is unique.

<=> In table_B (which I load afterwards) I have a finer grain and that same key is not unique anymore.

=> Will that work if I have, in the LOAD for table_B, a clause >> WHERE EXISTS([keyfield_from_table_A], [keyfield_from_table_B]) <<?

Thanks a lot!

Best regards,

DataNibbler

27 Replies
pratap6699
Creator
Creator

by using Exist()-->only matched records will be displayed in table

By using NOTEXIST()-->nonmatched records will be present in table

marcus_sommer

Maybe you have a distinct-statement in your loads - distinct has not only impact on the table-load in which it is written it also worked on loads which are joined, kept or concatenated to them.

It could be helpful if you put recno() and rowno() counter into your loads to count and show the loaded data and these one which are missing.

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

no, I have no DISTINCT. Well, I will have a closer look today - the reason I am actually using the EXISTS() is that I have a limited time_window in table_A which of course I would like to mirror in table_B, only table_B has no date_field.

I will have a look at the keyfields and the date_field from table_A - I assume (have a meeting now, will test later) that all those duplicates I have found are outside my date_window. That would explain why they are all gone afterwards.

=> If that should prove to be so, then I assume it is not a technical problem and my colleague would be better equipped to help me since he knows his way around our database.

Talk to you soon!

Thank you all for your persistence! I will report back in any case.

marcus_sommer

By using exists() it's quite important to consider the order of load-statements because it goes directly on already loaded field-values. Therefore you need sometimes to change your load-order to a maybe not so logical respectively chronological order and/or to drop/rename tables/fields to make sure that exists() grabed only to these fieldvalues which you want.

This meant in a more practically explanation that you couldn't load at first statement your master-calendar and makes a few script-tabs further any loads with an exists() on the date-field without causing strange results.

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

you mean that there must not be any other LOADs inbetween?

I can try changing the order of my LOADs around accordingly though I don't quite understand, since the table I have inbetween does not have the same field_name.

There is one LOAD inbetween for another table that I use to map one additional field and thus filter out a nr. of items - but I can do that afterwards just as well.

Other than that, I will now try to see if there really are any duplicates that lie within my time_window at all - otherwise, I can at least say that it's not a technical problem.

best regards,

DataNibbler

datanibbler
Champion
Champion
Author

Well, that was aparently not the problem - when I deactivate the LOADs inbetween the two, the nr. of records in question remains approx. the same - about 12.000 or so.

Well, the fact that this difference remains so stable is an indice that it might be correct after all.

Let's see ...

marcus_sommer

I want only mention it then I have had quite often cases where loadings are influenced from earlier loads which I had already forgotten and on which you think at last if something don't worked

- Marcus

datanibbler
Champion
Champion
Author

Thanks!

This is finally solved!

I have tried out another table - someone else told me I should be filtering in another table (I have that kind of filter already, but in the same table, apparently it is different) - I noticed that two values that I had actually filtered out were included if I did a JOIN with that table (with a perfect key in that new table, so that was ok).

=> Now I filtered in that new table as well and now     THE NR. OF RECORDS IS EXACTLY THE SAME!

I will close this thread now as I think the issue with EXISTS() is now kind of clarified, too.

With EXISTS(), there can be no issues with duplicates, only with NOT(EXISTS()), following the logic with the >previously loaded records<.

Thanks a lot once more!

Best regards,

DataNibbler