Combining Multiple columns into one column using beastmode

miguel_bustamante

I am making a table card that uses multiple different rows. The way I am doing it is creating a variable named 'unit type' that when set to specific values returns a specific column. It then sets all the rows for which that column is blank to null and finally I filter out any null values. bellow is the beastmode I used, how the data is structured, and an image of the output.

COALESCE((CASE
When Unit Type= 'Climate Controlled' then Climate Controlled
When Unit Type= 'Drive Up' then Drive Up
When Unit Type= 'Parking' then Parking
When Unit Type= 'Boat/RV Uncovered' then Boat/RV Uncovered
When Unit Type= 'Boat/RV Covered' then Boat/RV Covered
When Unit Type= 'Boat/RV Enclosed' then Boat/RV Enclosed
END), 'IS NULL')

I want to take it a step further and give our user the opportunity to set 'unit type' to All and have it display every unit type inside of the unitsize column as if all the data was being pulled from a specific column. I have tried using concat, but the output just combines the columns inside of the same row which isnt what I want. What I want to do is lets say I select all, climate controlled drive up etc would all be placed into one column and I would be able to see the data from all of them as if it was one column. please let me know how I may be able to do this.

ArborRose

I'm not sure if I understand your description or your data well enough, so this may be off track. But perhaps enough to assist.

if you have a calculated field like this (z_Combined_Units):

CASE
  WHEN `Unit Type` = 'Climate Controlled' THEN `Climate Controlled`
  WHEN `Unit Type` = 'Drive Up' THEN `Drive Up`
  WHEN `Unit Type` = 'Parking' THEN `Parking`
  WHEN `Unit Type` = 'Boat/RV Uncovered' THEN `Boat/RV Uncovered`
  WHEN `Unit Type` = 'Boat/RV Covered' THEN `Boat/RV Covered`
  WHEN `Unit Type` = 'Boat/RV Enclosed' THEN `Boat/RV Enclosed`
  WHEN `Unit Type` = 'All' THEN
    `Climate Controlled` +
    `Drive Up` +
    `Parking` +
    `Boat/RV Uncovered` +
    `Boat/RV Covered` +
    `Boat/RV Enclosed`
  ELSE 0
END

You can set that as a filter control, making sure "All" is included.

You can then filter the results based on the selection.

ArborRose

I'm not sure if I understand your description or your data well enough, so this may be off track. But perhaps enough to assist.

if you have a calculated field like this (z_Combined_Units):

CASE
  WHEN `Unit Type` = 'Climate Controlled' THEN `Climate Controlled`
  WHEN `Unit Type` = 'Drive Up' THEN `Drive Up`
  WHEN `Unit Type` = 'Parking' THEN `Parking`
  WHEN `Unit Type` = 'Boat/RV Uncovered' THEN `Boat/RV Uncovered`
  WHEN `Unit Type` = 'Boat/RV Covered' THEN `Boat/RV Covered`
  WHEN `Unit Type` = 'Boat/RV Enclosed' THEN `Boat/RV Enclosed`
  WHEN `Unit Type` = 'All' THEN
    `Climate Controlled` +
    `Drive Up` +
    `Parking` +
    `Boat/RV Uncovered` +
    `Boat/RV Covered` +
    `Boat/RV Enclosed`
  ELSE 0
END

You can set that as a filter control, making sure "All" is included.

You can then filter the results based on the selection.

miguel_bustamante

That method didnt work, but it did give me an idea that did. I was overthinking it. Instead of making the unit types as headers I made them their own column and then used a quick filter to only show the rows that matched the specific unit types i wanted.