Skip to Content
0

Rank function in BODS

Jul 12, 2017 at 10:53 AM

62

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Dirk Venken
Jul 13, 2017 at 02:03 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 12, 2017 at 12:26 PM
0

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).

Share
10 |10000 characters needed characters left characters exceeded
leo dec Jul 12, 2017 at 12:58 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded