Forum Discussion

patrice_cote's avatar
patrice_cote
Data Storage
11-07-2023
Solved

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...
  • Liliia_DevX's avatar
    Liliia_DevX
    11-07-2023

    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

  • Liliia_DevX's avatar
    Liliia_DevX
    11-08-2023

    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

patrice_cote's avatar
patrice_cote
Data Storage
11-07-2023

Thanks for your reply Lily,

Unfortunately I cannot do "max() over partition" because I must create this field in the ElastiCube. I import an excel sheet as a table and it's with this data that I must get the max rank by period. I tried the mathematical functions that you mentioned, it works to get the rank of each project for a given period, but I am not able to get the max rank for this period. If I was in Oracle I would simply use something like MAX(RK_OPP) OVER (PARTITION BY PERIODE), but I'm looking for a way to do this in Sisense.

I'll keep searching for a way to accomplish this, if you ever think about a way to do it I'd be grateful.

Thanks

Liliia_DevX's avatar
Liliia_DevX
Sisense Employee
11-08-2023

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

  • patrice_cote's avatar
    patrice_cote
    Data Storage
    11-08-2023

    Hello,

    This solution should work, I need to make some adjustments but I'm onto something!

    Thanks a lot