Calculated field for variables stored in an array

I have a field that contains data that is originally stored in an array form. This data shows the type of cuisines that a restaurant has.

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:

I’m thinking that it would allow me to filter out based on what I need as a singular tag column.

  1. I’m unsure how to do this
  2. I’m unsure whether this is the correct way forward.
    I’d love to be able to solve this and get started. Thank you everyone!
1 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!

1 Like

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

    ${tagCuisine}='Chinese', countOver(tagCuisineChinese, [outletId]),
    ${tagCuisine}='Filipino', countOver(tagCuisineFilipino, [outletId]),
    ${tagCuisine}='Western', countOver(tagCuisineWestern, [outletId]),
1 Like

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.

1 Like

@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

Hey @DylanM! I made a copy of my analysis on Arena

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:

ifelse(contains({tagCuisine}, ${TagCuisine}, CASE_INSENSITIVE), {tagCuisine}, NULL)

Then you would apply a filter like this to the visual to remove NULL values.

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!