Skip to Content

Rank function in BODS

Hi guys

I have gone through lot of forum question related to rank but only 1 looks what I am looking for

https://archive.sap.com/discussions/thread/3608785

I want to ask do I need to create sql function in sql server and then call sql server function in query transform in dataflow..

There is no in-build rank function in BODS.

I tried using gen_row_num_by_group(column name) but it is creating unique row number for each row for group id whereas I want it is should generate same number for group id same result expected in the forum question above mentioned.

regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jul 13, 2017 at 02:03 PM

    Use a Query transform to to number your records (use gen_row_num() built-in function). Add a 2nd Query transform, group by ID, and map min(rank) to rank. Then join the result with the original source, mapping the ID from the source and the rank from Query2.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 12, 2017 at 12:26 PM

    to use gen_row_num_by_group(column name) you should have your input sorted in the column name.

    Use a query transform to sort your data and then use gen_row_num_by_group(column name).

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 12, 2017 at 12:58 PM

    Hi Shubham

    I tried using gen_row_num_by_group but the result which is coming from DF is :-

    ID Rank_DS_Generate
    101 1
    101 2
    101 3
    102 1
    103 1
    103 2

    whereas result expected :-

    ID Rank_expected
    101 1
    101 1
    101 1
    102 4
    103 5
    103 5
    Add comment
    10|10000 characters needed characters exceeded