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

Shalini
Shalini Member
in Magic ETL

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

Best Answer

  • Shalini
    Shalini Member
    edited October 25 Answer ✓

    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 ;)

Answers

  • Jones01
    Jones01 Contributor
    edited October 23

    @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
    ArborRose Coach

    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.

    image.png

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach

    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).

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ggenovese
    ggenovese Contributor

    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
    Shalini Member
    edited October 25 Answer ✓

    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 ;)

Categories