Filtering table -- cells can have multiple values

jrtomici

I have a dataset table with filters. Some cells have multiple values. Say I have a column called "Device." Row 1 can have "Desktop, mobile" and Row 2 can have "Desktop." I want to have a filter called "Desktop" that shows all rows containing any instance of "Desktop."

When I do the beast mode formula:

case
when `Device` like '%Desktop%' then 'Desktop'
when `Device` like '%Mobile%' then 'Mobile'
end

this doesn't work, since with this method each row is grouped into only one of the new filters. So for example, if I filter by "Mobile" it will display Row 1, but if I filter by "Desktop" it will only display Row 2, since Row 1 is only associated with "Mobile." It should show both rows. Is there a solution to this?

MarkSnodgrass

This requires restructuring your data, so that there is a row for each type. So, if an entry had "desktop, mobile" it would become two rows with this ETL. It would look something like this:

You can use a formula in each of your filter to retrieve the rows that contain mobile and another one for desktop. Then create a column called DeviceType and set a constant value to it. Finally, append them together. Your filter card can now use that DeviceType column to get those distinct values. Your table card would need to use this dataset as well, but would give you the rows you are looking.

Hope that makes sense.

MarkSnodgrass

You might consider pivoting the data in Magic ETL to create columns for each of your types.

You could also use multiple filters in Magic ETL and then append them back together. This would keep your data from getting wider. You would set up a filter tile for each device type you are looking for and then use the append tile after those to stack your data.

jrtomici

Thanks for the response Mark. This filter would need to be a card used for salespeople to filter the table and create customized tables. Say a client wanted to know which packages (rows) support mobile devices. The salesperson would use the checkbox selector card to select "Mobile" and the table would display only relevant rows, which then could be exported as an Excel. Could you elaborate on how Magic ETL could be used to create a more robust filtering interface for users on the dashboard?

MarkSnodgrass

This requires restructuring your data, so that there is a row for each type. So, if an entry had "desktop, mobile" it would become two rows with this ETL. It would look something like this:

You can use a formula in each of your filter to retrieve the rows that contain mobile and another one for desktop. Then create a column called DeviceType and set a constant value to it. Finally, append them together. Your filter card can now use that DeviceType column to get those distinct values. Your table card would need to use this dataset as well, but would give you the rows you are looking.

Hope that makes sense.

jrtomici

That makes a lot of sense, thank you so much. Only issue is now, if multiple filters are selected, there can be repeat rows in the table. So if the user wants a table that displays only rows that support desktop or mobile, if both those filters are selected, any row that has both desktop and mobile will now appear twice.

Edit: I can hide these duplicate rows by sorting by device and aggregating by count, correct? Thank you so much for the help again.

MarkSnodgrass

You might be able to get around that duplication by not showing the newly created column in the main table and including an aggregate column in there. This way, it will just show distinct values. You can even hide the aggregate column in the chart properties, but it is still used to reduce the number of rows.

jrtomici

@MarkSnodgrass follow up question: I have two cards now, each checkbox filters that filter by the new columns made in Magic ETL since both these columns can have multiple values. However, when one of these filters is used, all the checkbox options in the other filter card disappear. How can I allow both filter cards to be used in conjunction and keep the card populated with values when the other is in use?

huanlin

@MarkSnodgrass As a side-note, thanks for being such an active member; I see your replies everywhere. Unfortunately, no post I've found has answered the question @jrtomici originally raised.

In response to your suggestions, I've tried creating an aggregate column to avoid row duplication, but this ruins the target filter column (even if it's hidden) since the target filter column now only contains one of the values (e.g. "Desktop" xor "Mobile" in this post's original case). This leads back to the original problem, where, as @jrtomici posted, the column contains only one of the desired values.

As an alternative approach to the problem, I've considered restructuring the data by splitting the column to something like "Is Desktop?" and "Is Mobile?" with Yes/No values, but then I would have to create two slicer cards for easy filter buttons, which is not only bulky but also undesirable because I've also heard DOMO charges by number of cards created.

Have there been updates to this problem in the past few months? Will I really have to create two cards to work around this problem?

jrtomici

@huanlin In the analyzer I sorted by the first column and that worked for me.

huanlin

@jrtomici Thanks for the quick response - can you elaborate on what the first column is? And what worked for you, the aggregation solution?

jrtomici

@huanlin Sorry--my table's first column is just the name of the row in the original data. It's the column whose duplicates I want removed, and to do this, I sorted by the COUNT of that column.