Filter options to exclude values not in the table

[gusbus14]

Hello -

I have loaded two tables into QlikView (ex. Table A, Table B). These two tables are linked on a field (ex. Field C). Table A has several values in Field C and some of them match up to those values in Table B. However, Table B has some values in Field C that are different than those in Table A, and vice versa.

I have two sheets, Sheet 1 with information from table A and Sheet 2 with information from table B. Each sheet has a filter for Field C. On sheet 1 I want the filter (search box object) to only have values that are present in table A, and on sheet 2 I want the filter to only have values that are present in table B. Yet, I want to keep these fields linked together in the data model because some of the info in Field C is the same, and in that case I want to be able to carry that selection over between sheet 1 and sheet 2. 

Is there a way to do this? It seems like set analysis may be useful, but I haven't figured out how to accomplish this yet.

Thanks

[chrismarlow]

Hi,

You can try using a function in the list box, so;

=If(Not(IsNull(A)),C,Null())

Gives;

And it still functions as a filter;

Cheers,

Chris.

[gusbus14]

Thanks Chris! This is useful, however I was hoping for a way to do this with a search object. Specifically because I like the drop down ability and the field that is being filtered in this case has about 20,000 values so I would want that drop down and easy ability to type something in and have the related values filter down in the drop down list.

[chrismarlow]

Hi,

I see. I think you would need to create a copy of your key field with a different name in each table, then use that, so just for table A (in my toy application);

TableA:
LOAD
	*,
	C AS [C for A];
LOAD * INLINE [
    A, C
    X, 1
    Y, 2
    Z, 3
];

Then the search object is limited;

But it still selects across the model (as associates through C). Any better?

Cheers,

Chris.

[gusbus14]

Chris - this looks like what I am trying to accomplish, I'm just not exactly sure I follow what is going on here. Could you elaborate at all?

Thanks

[chrismarlow · Accepted solution]

Hi,
So field C list box/search shows all values in field C in any table it appears in (that is what QlikView does). So in script here I am creating a copy of field C in table A, use that in the list box/search object and it will only show values on C in table A.
You would do the same for table B, calling the field something different.
Cheers,
Chris.

[gusbus14 · Accepted solution]

Chris -

I did end up doing this in the data model. However, I just used the set expression from the list box to create a new column in both table A and table B. The selection still carries over because the tables are connected on Field C which is used in the tables. Now each table has it's own field (Field C_TableA and Field C_TableB) so that those can be used as the filters.

Thanks for the help!