patrice_cote
Data Storage
Joined 3 years ago
User Profile
User Widgets
Contributions
Re: Need a way to add a partition as a field in a query
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. Thanks2.8KViews0likes2CommentsNeed 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. ThanksSolved2.9KViews0likes4Comments