Rank Function in Astera

Support Queries
, ,
1

Hello,

Wondering if there is a Rank() function available in Astera?
To give more context we want to perform below sql operation in the ETL.

RANK() OVER(PARTITION BY ID ORDER BY Last_Modified_Date desc) as ID_Rank

Thanks

2

Hello

Yes, you can use Rank() function in your SQL query.
Please see the attached screenshot for reference.

Thank you

Usman

14004
140041920×1018 204 KB

3

Thanks! But is there a way to actually utilize the tool to rank without using SQL?

4

Hello,

To achieve this, you can do the following steps:

  1. Import a source file(Excel in my case)
  2. Sort the data(I have sorted it as salary in Descending order)
  3. Then in expression transformation, I have added three fields and marked them as variables. In Field_3, I am adding 1 which will be 1 for first value and so on.
    In Field_1, there is a condition to check if the next salary is equal to the previous salary if it is equal then write the same value of Field_1 otherwise use the
    count value Field_3.

See the attached screenshot.

Thank you

Usman

14004-2
14004-21387×992 183 KB