topic Show excluded within selected in QlikView

Show excluded within selected

Fri, 17 Sep 2010 03:10:18 GMT

Hi,

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.

Any help with this would be greatly appreciated.

Show excluded within selected

Fri, 17 Sep 2010 07:04:01 GMT

HI,

Is this what you need:

If I select 1234 in table A , table B should display 3456,4567,5678

This can be achived using macro. Chect the sample code below:

Sub Test:

set val = ActiveDocument.Fields("TableA").GetSelectedValues

for i=0 to val.count -1

ActiveDocument.Fields("TableB").SelectAll

ActiveDocument.Fields("TableA").toggleSelect val.Item(i).Text

EndSub

Show excluded within selected

Fri, 17 Sep 2010 07:26:19 GMT

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.

Show excluded within selected

Fri, 17 Sep 2010 07:27:40 GMT

The GetSelectedValues function does not return any Account Ids because the field is not actively selected.

Show excluded within selected

vgutkovsky — Fri, 17 Sep 2010 07:55:34 GMT

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...

Regards,

Show excluded within selected

Fri, 17 Sep 2010 08:45:57 GMT

Oh I forgot about GetPossibleValues. I will give that a shot and come back.

Yes I have two Account Id fields. One in each table.

Show excluded within selected

Tue, 21 Sep 2010 11:19:29 GMT

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.

Show excluded within selected

vgutkovsky — Tue, 21 Sep 2010 17:34:04 GMT

See attached solution.

Regards,

Show excluded within selected

Wed, 22 Sep 2010 08:08:32 GMT

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

Show excluded within selected

vgutkovsky — Wed, 22 Sep 2010 14:19:14 GMT

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.

Regards,

Show excluded within selected

Fri, 24 Sep 2010 03:09:08 GMT

I was actually hoping for a solution with the two tables joined by a copy of the Account Id itself, but I didn't think it was possible to do this kind of thing when they are joined due to the selections cancelling out the differences anyway. If there is a solution where they are joined it is much better because my other table that I have created runs a lot faster when the tables are joined.

I will have to post a sample app later when I have a bit more spare time.

Show excluded within selected

Mon, 27 Sep 2010 05:48:50 GMT

I have attached a sample app which covers part of what I am doing.

There are ~30,000 rows of test data there (mostly random generated).

The fields that are required for comparison are copied to another table. The fields that are just for filtering are just left in the first table.

The user tables compare movement of customers from one band to another between the two selected periods. One lists the total value of the second selected band and which band that value has moved from. This isn't really useful information in my opinion so I convinced the users to make the second table. The second table contains the difference after moving from one band to the next.

So these tables are for Accounts that are in both periods, but the users then want a list of Accounts that have been cleared (so are in the first selected period but not the second) and a list of new Accounts (in the second period but not the first).

Show excluded within selected

vgutkovsky — Mon, 27 Sep 2010 19:20:08 GMT

See attached, hope it helps.

Regards,

Show excluded within selected

Tue, 28 Sep 2010 03:03:33 GMT

Thanks Vlad, that is great. I am still trying to understand the expression, but do you know why the Customer Group filter makes the second list box (for the To Month) list all Account Ids when selected? I assume it has something to do with the filter being applied to the first table and causing the expression for the second table to break.

Show excluded within selected

vgutkovsky — Tue, 28 Sep 2010 06:33:26 GMT

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.

Cheers,

Show excluded within selected

Tue, 28 Sep 2010 10:31:11 GMT

Thanks, that works great 🙂