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

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.