Need a way to add a partition as a field in a query
Hello,
We are using Toad for Oracle to develop our sql views, which we bind to Sisense using ElastiCube. For this case, we have an external source of data, I created a table using an excel sheet and we have a custom table using a query created directly in the ElastiCube so that we can work with the excel sheet. The situation would be a list of projects (PROJ_NO) with their cost (COST) for each date (PERIOD). A period would consist of a year and a month, like 202311. I need to rank each project according to it's cost by period and also get the max rank for each period. For the rank I used RankDenseAsc(PERIOD, COST) and it seems to do the trick. But I am struggling to find a way to get the max rank for the current period. In Toad for Oracle it is accomplished by doing MAX(RK_PROJ) OVER (PARTITION BY PERIOD), RK_PROJ being the rank calculated previously for the current period. There has to be a way to do this in the ElastiCube. I'm all ears if you have any idea.
Thanks
Hi Patrice,
Please note that the following rank functions are currently supported on the ElastiCube level in Sisense:
RANK(), RANKASC(order_by_field), RankCompetitionAsc(partition fields list, order field), RankCompetitionDesc(partition fields list, order field), RankDenseAsc(partition fields list, order field), RankDenseDesc(partition fields list, order field), RANKDESC(order_by_field).
The detailed description of these functions could be found in this guide - "Mathematical Functions".If these functions do not allow you to achieve your goal, you can run the query with "max() over partition" in the custom import query so that it will be executed on the datasource side where this function is supported and then return the result dataset to the table in ElastiCube.
Best Regards, Lily
Hi,
Thanks for the detailed explanation. Since the function over partition is not currently supported in ElastiCube manager within a custom table, please try to use an alternative SQL queries to achieve the same goal. An example SQL code that might work is added below:
SELECT t1.partition_column, t1.value_column FROM your_table t1 JOIN ( SELECT partition_column, MAX(value_column) AS max_value FROM your_table GROUP BY partition_column ) t2 ON t1.partition_column = t2.partition_column AND t1.value_column = t2.max_value
This query will return rows with the maximum "value_column" within each partition defined by "partition_column" without using the "MAX" function over the partition directly.
Best Regards, Lily