Variable holding a list

HolySpecs
HolySpecs Member
edited April 2023 in Variables

I want to implement a variable that has a list of countries for a region. Something like:

AsiaCountries = ["Japan","China","South Korea"]

EuropeCountries = ["Spain", "France", "Germany"]

Is there any way to do that for Domo variables?

Answers

  • ST_Superman
    ST_Superman Domo Employee

    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 :)

  • TylerMarshall
    TylerMarshall Member
    edited April 2023

    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 with

    REPLACE(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:"

  • HolySpecs
    HolySpecs Member

    @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'


    END

    and I want to replace the countries that is in the IN bracket with a variable, where a list of countries can be added.

Categories