Hi All, I'm new to domo and I've a question on Table joins.

Shalini

I have two tables table A and table B. Table A is my fact table and Table B is a dimension table. I should do left join since Table A is my fact table. The issue when I do it the total bookings and capacity value in Table B is getting duplicated. I know this is due to many to 1 relationship. when I calculate Total bookings percentage this is giving me wrong values in the card. Can you tell me how to solve this issue ? I've attached here the table pictures and resulting output table I'm getting after I do the left join.

Also I wanted to know, do we need to use only 1 output data set to build cards ? (or) we can use multiple output datasets to build cards and put it in dashboard and does filters work on those cards ?

img-0909.jpg

Shalini

Hi Community,

Finally I found the beast mode to achieve this calculation it goes like this,

MAX(`Capacity`) OVER (PARTITION BY `property_id`)

1. I used the group by tile in Table B to sum the Capacity and Total bookings of table B to get the sum of Capacity and Total bookings based on the property ID.
2. Then left joined the Table A (Left Table) with Table B (Right Table) with `Property id` as a Key column and it gave me the duplicate values of total capacity and total bookings in the right table since the relationship between Table A and Table B is Many to One.
3. Then I used the above Beast mode in the Output Dataset to get the Total capcity and Total Bookings for each property ID and it worked out! Hope this post will be helpful for someone facing the same issue. Thanks all of your great response, appreciate it ;)

Jones01

@Shalini hi, are you joining on property id and check in date? it looks like you may be only joining with the property id.

on a dashboard the cards can be using different datasets but as long as the column names match then filters will works across all of them where it can find a column with the same name

.

ArborRose

Think of a dashboard as a collection of cards. You can have several cards on the dashboard. They can be text/table cards, charts, etc. Each card would use a single dataset.

If you are joining the datasets, you would create a Magic ETL to handle the logic. In this brief example, we would take two datasets, join them and handle the aggregates with a group tile. Say for example, we want to group totals by date.

Our card would use the output dataset (factdimensions_test).

Inside the card, you can do filtering. Or, you could add the card to a dashboard. And add a filter card to the dashboard. The filter card would affect all cards on the dashboard that have the common dataset and field name of the filter.

ArborRose

You put multiple cards on a dashboard and the filters work across the cards. In this example, the main card is the green outline at the bottom. The three cards at the top are for filtering. They have chart type: filter. Each of the cards use the same single dataset (result of an ETL or an import, etc).

ggenovese

I realize you may only posting a subset of your data, but given what you've shared it seems as if you may want to union (in magic ETL it's called append) Table A and Table B. this would give you all the check in dates and property id's without duplicating bookings and capacity.

Shalini

Hi Community,

Finally I found the beast mode to achieve this calculation it goes like this,

MAX(`Capacity`) OVER (PARTITION BY `property_id`)

1. I used the group by tile in Table B to sum the Capacity and Total bookings of table B to get the sum of Capacity and Total bookings based on the property ID.
2. Then left joined the Table A (Left Table) with Table B (Right Table) with `Property id` as a Key column and it gave me the duplicate values of total capacity and total bookings in the right table since the relationship between Table A and Table B is Many to One.
3. Then I used the above Beast mode in the Output Dataset to get the Total capcity and Total Bookings for each property ID and it worked out! Hope this post will be helpful for someone facing the same issue. Thanks all of your great response, appreciate it ;)