I have a table like
| name | value1 | value2 |
|---|---|---|
| john | a | x |
| kevin | b | y |
| larry | c | z |
I want to pass the parameter with dropdown values a,b,c,all where if user selects a then output is first row or if user selects all then also the out is all three rows. Please suggest.
You need to follow the documentation on creating the parameters for Databricks SQL. When editing the query, click on the {} icon to insert a new parameter into query, then select "Dropdown List" as a "Type", and enter possible values. Mark a checkbox "Allow multiple values" if you want to allow to select multiple values. And if you're using strings, timestamps, or dates, then select corresponding value from the "Quotation" dropdown:
and then modify your query to be something like (actions is the name of the parameter):
where action in ({{ actions }})
Then you can select necessary values from the dropdown:
and execution of the query should give you something like this:
Solution in PySpark:
Choise_List = map(lambda x: str(x[0]), <Dataframe_Name>.select("value1").distinct().collect())
dbutils.widgets.dropdown("a", "a", Choice_List)
Note: Change the <Dataframe_Name> with your actual dataframe name.