Combining Table Results in Orbit using a CubeLookup Expression

Combining Table Results in Orbit using a CubeLookup Expression

Sometimes it is useful to be able to get results from a Table in Orbit and use them in another Table. Within Orbit, it isn't currently possible to refer to results in one tile within another one.

However, we can use a combination of some powerful Expression functions and Virtual Variables in FastStats in order to make this possible.

As an example, the following Dashboard features two Table tiles. They both have the Occupation variable as the row dimension.

Let's say we wanted to get the column values from Table Two as another measure in Table One:


To do this, we need to log into the same system using FastStats and we need to create the Table Two example from within FastStats:



We can use the CubeLookup function in order to get at the values for each Occupation category. To do this:

Click the Expression tool
Drag and drop the Cube onto the Expression window
This will add the Cube as a CubeLookup() function, similar to this:



In general, the CubeLookup function has the syntax:

CubeLookup({Cube Name},[Measure Number], [Dimension Name])

If you click the CubeLookup tab then you can choose which Dimension and which Measure to reference. In the example above, there is only one measure which is People and there is only one Dimension which is Occupation, so we do not need to specify any of these two parameters.

What the above function does is look up the values in the row dimension (Occupation) and look up the value in the People measure for that value.

In this additional example below, we have two measures. If we wanted to return the Sum(Cost) measure:



We could click the CubeLookup tab, uncheck the Default Measure option and choose Sum(Cost) from the Measure listbox control:



which would produce the following Expression:

CubeLookup({Cube #1 - Table Three},2)

where the ',2' is referring to 2nd measure i.e. Sum(Cost).

Returning to our example, which only has a single measure, we need to click the button shown in red below in order to create a virtual variable from this CubeLookup function:



If we create a virtual variable then what this effectively does is record the value in the Cube for every record with that particular category. So, if a person has 'Manual Worker' as an Occupation then we will store 119 in their record. If they have an Occupation of 'Director' then we will store 60, and so on. We need to proceed through the virtual variable wizard in order to create the variable. In this example, we can produce an integer value as the number of People is a whole number. I named this 'Table Two Measure'.

Now that we have this as a numeric variable then we can add it to any other Table, Cube or any other Tile in the same way as any other variable. The key though is to add this as a measure in the left-hand Table but specify a function which only returns a single value; if we chose the default function of 'Sum' then Orbit would total up all of the values for that category whereas we want a single value. Therefore we need to choose a function like Mean, Minimum or Maximum to restrict it to just a single value per category.

To add this measure to the Table on the left-hand side, edit the Dashboard and edit that Tile:

Click the +Measure option
Search for the virtual variable you created earlier (in my example it was named 'Table Two Measure')
Set the Function to be Mean (or Minimum or Maximum)



and click Apply

The People value from Table Two is now available in Table One:


One thing to note is that this value is static. In the example below, I have filtered the dashboard so that there is only a single record showing in Table Two but the original values are being shown in the Mean(Table Two Measure) column within Table One:


This is because the values in the virtual variable are loaded based on the Cube at the time of the data load. When the data refreshes then the Cube values will change and the VV will update accordingly but it will not be affected by any filters applied to the dashboard.



    • Related Articles

    • Expression Index Geo Nearest

      An issue arose when a selection was created from an Geo Nearest Expression using an index. The problem was that the place was not corresponding to the coordinates on the map Visualisation. E.g. When choosing Warwick in the list on the selection and ...

    • Counting specific days \ day types between 2 dates.

      A user asked it was possible to count the number of working (weekdays) between 2 dates. They had considered the function Datediff() within an expression but realised this simply counts the total number of days between 2 dates. Our suggestion was to ...

    • Problem using Post Load Action (Combine Categories) with data from Snowflake

      A user reported that they were in the process of rebuilding a system from a new ODS (Organisation Data Source) within Snowflake. The data contained 3 separate ID variables together with descriptions and the user was aiming to merge these as a ...

    • Error Message with Login Service on new Orbit setup

      A user queried a message received in Orbit whilst moving a system to AWS. Everything seemed to be working correctly so he questioned the message given: Our answer was that the Login Service is something we use as part of the SSO login process. ...

    • Changing the default email address for the Orbit Administrator

      By default the Administrator user for each Orbit system is created with an email address of support@apteco.com This is done for assistance and reporting purposes e.g. for assistance when setting a new system up. A side effect of this is that any ...