Creating a beastmode that filters

ColinHaze

Hi,

I am having a hard time with this simple task.

I have a column in my dataset called Lane, which is formatted as followed 'PointA>PointB.'

I am trying to create a card on a dashboard that if PointB is selected it will filter all lanes that either start with PointB or end with PointB (exmple 'PointA>PointB' would show up and 'PointB-PointC' would show up.)

Ive tried creating a beastmode which is called CitySelector which its just the shipperCity column. I then tried to create this beastmode but its only getting lanes that start with whats selected in the CitySelecor Filter. Any Ideas why?

CASE WHEN Lane LIKE CONCAT('%', CitySelector, '%') THEN 'Y' ELSE 'N' END

Thanks for the help!

Manasi_Panov

Hi @ColinHaze,

How about using variables for this with a search field? Would it be a viable solution? Here's an example.

DavidChurchman

Short answer, no.

Manasi suggested using text-input as the control, which is probably the easiest way for this use case.

For a drop-down, once you laboriously populate the variable, it can be a pain to make sure the values you have populated for the variable on your card are the same as the ones you have in your control, as each instance of a control for a variable can have a unique list, and it does not stay synced with any other list. So if your city list changed at all, it would not be dynamic, and even manually updating it can be buggy.

Manasi_Panov

Hello @ColinHaze,

Create a Search Variable like this:

Create a CASE statement in Beast mode like this:
CASE WHEN `Search Filter` = '' THEN 'TRUE' ELSE (CASE WHEN `Lane` LIKE CONCAT('%',`Search Filter`,'%') THEN 'TRUE' ELSE 'FALSE' END) END

Add Beast mode formula to ‘Filter’ with the following settings:

DavidChurchman

It appears logically sound. Can you give some examples of city/lane combinations that worked in your filter and ones that did not work?

I suspect it's some text formatting thing. Wrapping your variables in UPPER() and TRIM() might be good, to make sure it's not a case/white space issue. If that doesn't do it, I'd need to see when it's working/not working to be able to de-bug.

Manasi_Panov

Hi @ColinHaze,

Could you please show the values in 'shipperCity'?

ColinHaze

Manasi_Panov

Here is some sample Data. CitySelector is a beastmode that is the shipper_city_State Field.

DavidChurchman

When I select a certain city off the cityselector, only those with matching shipper_city_states appear on the card, none with matching consignee_city_states appear.

Manasi_Panov

Hello @ColinHaze,

The issue likely arises because 'Shipper_City_State' and 'CitySelector' have identical values, causing the 'CitySelector' filter to also apply to this column and exclude all rows where 'Consignee_City_State' matches 'CitySelector'.

Manasi_Panov

Hi @ColinHaze,

How about using variables for this with a search field? Would it be a viable solution? Here's an example.

DavidChurchman

I see the problem with your approach. Your filter is looking for rows where the CitySelector is in the Lane, but because the CitySelector is only the Shipper_City_State, there are no rows that exist where the CitySelector is the same as the Consignee.

For this approach to work, you'd need to double the length of your dataset, and have one lane where the CitySelector is the Shipper and a duplicate lane where the CitySelector is the Consignee.

Alternatively, @Manasi_Panov 's variable approach is sound, though you would rely on people being able to type out city names.

This is also a use case where being able to combine filters with an "OR" would be really helpful, which has been suggested a handful of times on the ideas exchange, although I'm sure a few more times wouldn't hurt.

ColinHaze

Manasi_Panov & DavidChurchman
Is there an easy way to populate the variables without having to manually enter all the citys in?

DavidChurchman

Short answer, no.

Manasi suggested using text-input as the control, which is probably the easiest way for this use case.

For a drop-down, once you laboriously populate the variable, it can be a pain to make sure the values you have populated for the variable on your card are the same as the ones you have in your control, as each instance of a control for a variable can have a unique list, and it does not stay synced with any other list. So if your city list changed at all, it would not be dynamic, and even manually updating it can be buggy.

ColinHaze

Manasi_Panov & DavidChurchman

Thank you both! Im gonna try out this text variable and see how it goes.

Manasi_Panov

Hello @ColinHaze,

Create a Search Variable like this:

Create a CASE statement in Beast mode like this:
CASE WHEN `Search Filter` = '' THEN 'TRUE' ELSE (CASE WHEN `Lane` LIKE CONCAT('%',`Search Filter`,'%') THEN 'TRUE' ELSE 'FALSE' END) END

Add Beast mode formula to ‘Filter’ with the following settings: