RANK in BODS using gen_row_num_by_group() function

Hello,

I am trying to explain Ranking in BODS. It plays very important role when your Business Requirement asks you to get most recent value within a group of values. We have a function called gen_row_num_by_group function() in BODS. With the help of this function you can generate RANK and then get Recent or Last value.

Go through the screens, it will tell you every step that you have to follow.

For Example:  Let’s take a scenario a customer has ordered few products on different dates. Now your business requirement wants to pick most recent date from Order date.

Now what you need to do is put order date in order by. That could be Ascending order.

Now Add a column you can name it as RANK and then map oreder_no with gen_row_num_by_group() function. It looks like below screen.

Now you run the job, you will get result like below table.

Now create another DF and make RANK TABLE as source. Map Oreder_date with max() function to pick most recent one. You would need group by Clause.

You have successfully generated RANK and picked up most recent date. Your result would look like below tables.

Regards,

Imran