Using a lookup to create a Filter

[JF]

I have a table of all cost centres ID and their associated descriptions.

I have several other tables that contain different subsets of the IDs (but no description) and are classified as Reports.

How do I create a dynamic filter that would only display the list of descriptions based on the IDs in any particular subset?

 E.g.

Cost Centre Table

ID, Descr

1, Finance

2, Sales

3, Marketing

4, IT

5, Call Centre

I have a filter using Reports. Sales Report contains IDs 2 and 3 only. Support Report contains 4 and 5, All contains 1,2,3,4 and 5.

If I were to select Sales Report, the Cost Centre Filter should display Sales, Marketing in the dropdown and if I were to change the Reports filter, then the Cost Centre Filter would update accordingly.

This is a made up scenario to explore the capabilities of QlikSense.

Thank you.

[OmarBenSalem]

I don't understand.

The filter that contains values :'sales reports' and 'support reports'.

How is it created?

How is it associated to the IDs?

[JF]

Hi Omar. Thanks for looking in.

Let's assume the tables are:

Report_Type, Cost_Centre_ID, Amount

Sales, 2, 4

Sales, 3, 7

Support, 4, 5

Support, 5, 5

I believe we can create a filter based on the field Report_Type. The 2nd filter is where the question revolves:

1. I would like to limit the available options based on the 1st filter. So if I click on sales, it will only show 2 and 3.

2. I would like it to display the description instead of the ID, so it'd need to do a lookup.

[OmarBenSalem]

Assume u have another table as follow:

ID, description

2,desc2

3,desc3

and thus the 2 tables are associated by the ID field.

If u select Sales, and have the description as a filter, the associative logic of Qlik will only keep the description values : desc2 and 3 associated to the ID 2 and 3 which are associated to ur selection 'Sales'.

In Qlik Sense, the associated values to ur selection are "colored" in white, the alternative in Grey.

Now, if u want to only keep only the associated values (the white ones), u do : 

aggr(only({<description=p(description)>}description),description) 

instead of description, as a filter pane.

I Hope that was the question?

[JF]

So it is possible then? And what you're suggesting is that this can be done via "set analysis"?

Fantastic! I'll give it a shot.

Thank you.