The goal is to create a calculated field that reduces this array down into simpler cuisine tags, such as western, thai, indian, chinese, etc. The issue is some restaurants share similar tags, such as having western and italian in the same array tag, or western and japanese in the same array tag. How do I create a calculated field that displays a single type of cuisine even if the string (which is originally an array) consists of various different cuisines?
My original thought process was to split up this array into various columns. So it’ll look somewhat like:
Hello @rohit_SB, so the best way to accomplish this will be to utilize an ifelse statement in a calculated field and check if the value exists using the contains function. You will need a calculated field for each column, but I will provide an example for how one of them will look.
Chinese = ifelse(contains({Tag Cuisine}, 'chinese', CASE_INSENSITIVE), 1, 0)
This should allow you to check for each scenario by Restaurant and determine if the matching tag is present. I used CASE_INSENSITIVE in case the way the word is typed is different on some rows, but if it always matches you can use CASE_SENSITIVE. This should provide you with the output you are expecting! I’ll mark this as the solution, but please let me know if you have any follow-up questions. Thank you!
Hi @DylanM, thank you for the response. I actually managed to get this down yesterday. However, the follow-up question would be:
How do I use these cuisine fields as a ‘category’ to filter by? I currently have about 25 fields for 25 cuisines. Do I use a parameter to filter? I’d like for it to be a single field that would allow me to filter based on my preference (multiselect dropdown). What would you suggest?
here’s what I’ve done, but it doesn’t seem like the correct way to go forward. My outlet (supplier) table is joined to the orders table, which means that I get values much larger than what’s true, hence why I’m using the LAC-W function. However this seems wrong
Hello @rohit_SB, how exactly would you want the filter to manage the visual? If you filtered for Chinese and nothing else, would you only want the Column containing the Chinese food tag to be returned in the visual? That will start getting more complex and will likely run into limitations if you are wanting to show and hide full columns in the visual based on the user selection.
@DylanM That’s precisely how I would prefer to show the columns. If you select for Chinese, only restaurants with Chinese food show up, i.e., filtering for specific columns based on requirement. Is there another way to go about this since this method could get complex? Maybe a technique to create a single calculated field to show all different types of cuisines? I’m unsure how it would work since a single restaurant can have multiple cuisine types
Hello @rohit_SB, in regards to the map visual, something you could do if you have a custom list dropdown control selection, you could utilize a calculated field to filter for just the tags you are looking for. It could be something like this:
Then for your pivot table, you can utilize this to filter as well, while still keeping the columns for each cuisine tag type. It won’t elimate columns with empty values, but it should at least reduce the number of rows returning. I’ll mark this as the solution, but please let me know if you have further questions!
