Create a dynamic drop down list

[andymanu]

Hi All,

I got a table which got two fields ( Country and City). Please find the attached excel sheet.

What I want to do is, list the countries in a table and if a user selects a country, the respective cities which belongs to the country should pop out as a drop down list. Refer below.

Similarly, if I select a different country, the respective cities should pop out.

I am hoping to create a dynamic variable for the country field and add the respective cities as the values for respective variable.

Without using ApplyMap() and pivot table, is there a way of getting around this please?

Appreciate your feedback.

Kind regards,

Andy

[Mauritz_SA]

Hi @andymanu 

I'm not sure what your requirement is, but the following should get you going:

1. Create a variable with name vCountry.

2. Add a standard variable input component (from the Qlik Dashboard Bundle) with the following configuration:

3. Create a filter pane with dimension =AGGR(ONLY({<Country = {'$(vCountry)'}>} City),City)

When you select a new country from the drop down, the filter pane only shows the cities in the selected country.

I hope this helps.

Mauritz

[andymanu]

Hi Mauritz,

Thanks for your respond.

However, just to get my head clear around the issue, firstly, I need to create a variable named "vCountry" and add the "Country" field as the definition.

Then I'll add a "Variable input" to the visualization dashboard and do I need to specify any parameter values? For example,

Name = vCountry

Show as = Drop down

Values = Fixed

Upon doing the above said, Adding a Filter Pane to the dashboard and set the expression to "=AGGR(ONLY({<Country = {'$(vCountry)'}>} City),City)".

Am I reading you correctly?

However, with reference to the above, I am not getting the desired output.

Appreciate your valuable help.

Basically What I am looking at is;

When I open the app, I should see only the Countries at the first instance. For en example refer below;

When I click on a country, the respective cities should pop up under the selected country. For example, "Australia"

Similarly, assume the user selected the country "New Zealand" the out put would looks like below;

Apologies for the clarity issue. Hope now the question is much clear.

Look forward to hearing from you.

Thank you.

Kind regards,

Andy

[Mauritz_SA · Accepted solution]

Hi Andy

Sorry, I see that the picture disappeared when I made the post. Below is what I meant to send (I've attached it as well in case it doesn't send again):

What is the use case? Maybe then it will be easier to try and help you. What I explained in my post was how to have a drop down at the top with all the countries. As soon as you select a country, the relevant cities will be displayed (which are available for selection).

Below is a picture of the end result:

Regards,

Mauritz

[andymanu]

Hi Mauritz,

Thank you very much for your reply and sorry for delay in replying.

Yes, this serves my purpose and my ultimate objective is to visualize the data which are related to the "Cities" under each country when a user is selected.

For example, assume I got some population related data for each city, when I select a Country (from the drop down list) I want to show the population to the respective country and when narrow down the selection to the "City" level, only the population related to the respective City should be displayed.

Note

I also could have use a Filter Pane and add the two dimensions "Country" and "City" into it. However, when the number of Countries and the Cities grows bigger, it will looks ugly in the visualization window thus your method would be handy and easy to navigate through.

Thank you once again.

Kind regards,

Andy

[Mauritz_SA]

Hi Andy

No problem, glad I could help. What I would recommend is that you use the vCountry in set analysis so that the country selection actually also reduces the data. You can then possibly also add an All Countries option where you can use something like '*|All Countries' & Concat(Distinct Country,'|') to allow the end user to see data for all countries if they want to. I don't have QS open at the moment so I can't test, but this just came to mind when I read your use case.

Regards,

Mauritz

[andymanu]

Hi Mauritz,

Thanks for your valuable guidance.

I'll do accordingly and will get back to you if come across an issue.

Thank you.

Kind regards,

Andy

[NikosSpanos]

@Mauritz_SA  Is it feasible to have multi-selection in the drop down list? Because your approach is for single-selections only. By the way it works fine.