Variable holding a list
Answers
-
This is an interesting ask. I'm not aware of a way to do this with variables. I would probably recommend adding this data to the dataset via an ETL and then you could use a combination of filter cards (one for region and one for Country) to accomplish this on a dashboard. However, maybe you could reach out to @chucki to discuss your use case. He may have something cooking in the beta program that we don't know about :)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Are you wanting to be able to filter based on a list of items? Try this:
CASE --Checks from list using “:” to determine start and end of item name WHEN CONCAT(':', REPLACE(UPPER(REPLACE(`Var.ItemList`, ',', ' ')), ' ' , ':'),':') LIKE CONCAT('%:', UPPER(`ItemName`), ':%') THEN 1 --Checks for direct match WHEN UPPER(`Var.ItemList`) = UPPER(`ItemName`) THEN 1 --If nothing is entered, display everything WHEN UPPER(`Var.ItemList`) = '' THEN 1 ELSE 0 END
What this does is replace all blank spaces and commas with colons to seperate each item the user adds to the variable. It then checks if the column in the dataset you're referencing matches an item in the itemlist variable. Additional checks are added if the variable is an exact match to a column (if only 1 item was entered) or if it's left blank (nothing is filtered in this case).
For example, variable has input of "SPAIN, France, Germany", this is convered to ":SPAIN::FRANCE::GERMANY:" and then checks for a match with the "ItemName" column.
If you're wanting the variable to be a static list, you can set the variable to be defaulted to whatever you like. From that point, any time your reference that variable, it will be your static list.
One thing to note, with your example, "South Korea" contains a space which will cause this solution to search for "SOUTH" and "KOREA" seperately. I haven't tested this but you can likely swap the "UPPER(`ItemName`)" section on line 3 withREPLACE(UPPER(`ItemName`), ' ', ':')
In theory, this will convert "South Korea" to "SOUTH:KOREA" and create a valid match when compared to the variable ":JAPAN::CHINA::SOUTH:KOREA:"
0 -
@ST_-Superman-_ for more context, the reason I want to use variables because I have a Beast Mode that looks like the below:
CASE
WHEN Region = 'EMO' THEN
CASE WHEN Contact Country IN ('Belgium', 'Finland', 'France','Germany',
'India','Ireland','Israel','Israel',
'Italy','Netherlands','Spain','Sweden',
'United Kingdom') THEN Contact Country
ELSE 'Other'
END
ELSE 'Other'
ENDand I want to replace the countries that is in the IN bracket with a variable, where a list of countries can be added.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive