I have two tables. Both have Account IDs. A user can make selections within either table. What I need to do is display a list of Account IDs in the first table that are selected but not within the selection in the second table.
Eg.
Table A Table B
Account ID Account ID 1234 1234 2345 3456 3456 4567 4567 5678
My resulting display table should then only contain 2345.
Of course this is easy within a load but I need this to be dynamic within a selections that a user makes. It seems like a simple thing but I just can't figure out how to get a result, maybe I am just missing the obvious.
The background for this is that users can select one quarter then a second quarter and would like to then have one table displaying Accounts that have been cleared from the previous quarter to this new quarter, and another table displaying new Accounts that just popped up in the new quarter. I cannot do this within a load because I don't know which quarters the user will choose.
This is happening because the 2nd expression uses the COPY_ACCOUNT_ID field which is located in a different table than CUST_GROUP. I've updated the application so selecting the group doesn't break it anymore. Pay particular attention to the expression in the Customer Group listbox, because that's important.
Account ID is not itself selected. Other fields within the two tables are selected eg. Quarter, Year, Customer Group etc.
So then we have two filtered tables with two sets of rows. Both have an Account Id field and I want to compare those two fields and get the Account Ids from the first filtered table that are not in the second filtered table.
I'm confused. Do you have 2 fields or 1? You reference Account Id twice, but logic like this is only even possible if you have 2 separate fields. Assuming you have 2 fields, GetPossibleValues in a macro should then accomplish what you need...
I have tried the GetPossibleValues method, but the nested FOR loop that you have to do to check the two fields against each other is not efficient enough, it takes hours with the amount of data I have.
Thanks for that solution. It works with our smaller data sets but unfortunately not for the medium-large sets. This is potentially a few hundred thousand Ids so Qlikview cannot handle the processing with that expression (on my test server it ate up 50GB of memory in 5mins then just stopped).
Yeah that is an extremely heavy expression because of the disjoined fields and the nested aggrs. Since the 2 IDs are not linked in any way in my data model it creates a Cartesian join situation where every possible combination is created in a temporary table. If you have 200,000 of one ID and 300,000 of another, then it tries to create a temporary table of 60B rows. I'm not surprised that it's failing if you have that much data! If your data model is 2 completely disjoined IDs then there is not much else we can do. However, if the 2 IDs are somehow linked then maybe another solution is possible--please post a small sample of the app.
