This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
on - edited on by
DRay
**Please refer to the following article for extended functionality: Performing a Front End Left Join **
This article presents a data modeling based solution that may allow you to create a simple Left join within one certain widget.
The following article will explain how you can create a simple 1 dim to 1 fact left join in your widgets.
The Challenge
By default, the queries generated by Sisense are creating an inner join, thus excluding all values that do not exist in both of the joined tables. Imagine the following scenario:
In our Elasticube, we have a Products dimension table and a sales-details fact table.
Due to the inner join, every widget that will fetch fields from both tables will only fetch records that have a full match. For example: If we try to query a list of products and the revenue yielded from their sales, we will not see any reference to products that were never sold.
In most cases, this is the required behavior. However, in others, the lack of activity can tell us a lot about our business and low performing items.
Implementing the Solution
1. In your Elasticube, find the dimension, or the master table from which you want to initiate the left join.
2. Add a new custom field of type Int.The value of this field should be a hardcoded 0:
3. Build your Elasticube to submit the changes.
4. Open your dashboard of choice and create a new widget. In the Categories\Rows panel, choose any field from the master dimension table
5. Create a new value and go to the formula menu:
6. Find the dummy field from your dimension table, and sum up its value. Add that aggregation up to the original measure from the fact table:
This combination will provide you with all of the values from your dimension, whether they have recorded activity or not:
Identify Items with no Activity
Once the Left join field is added and the "left" value is defined, you can create a widget that would list all items that have seen no activity. In our example, products that were not sold at all.
To do so, follow these steps:
1. Hover over the newly created value and click on the funnel icon to create a value-based widget filter:
2. set your filter to 0:
Your widget will now list all items with no activity.
If you would like, you can now disable the value and leave only the "Rows" active. As long as you don't change the filter, it will still remain valid.
