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.

RANK.png

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.

/wp-content/uploads/2015/04/2_695820.png

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

/wp-content/uploads/2015/04/3_695821.png

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.

/wp-content/uploads/2015/04/4_695825.png

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

/wp-content/uploads/2015/04/5_695826.png

Regards,

Imran

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply