BusinessObjects Board

How to generate same numbers for a group of data

dataservices job design
#1

Hi,
i need to generate same numbers to the set of data in a group.

My Source table holds

PRID TRID
5 2
9 5
10 5
15 7
45 9
89 9
97 9

I need to generate a group id with below data

PRID TRID GroupID
5 2 1
9 5 2
10 5 2
15 7 3
45 9 4
89 9 4
97 9 4

when i used gen_row_num_by_group() i am getting the groupid data in the below table order,but i would like to get the groupID data in the above given table order.

PRID TRID GroupID
5 2 1
9 5 1
10 5 2
15 7 1
45 9 2
89 9 3
97 9 4

Any idea on how to do this.

Thanks

kiran344 (BOB member since 2010-04-12)

#2

gen_row_num() - gen_row_num_by_group() + 1 ?

HerdplattenToni (BOB member since 2011-05-13)

#3

Hello Toni,

Thanks for your reply,
do i need to include column ‘‘TRID’’

gen_row_num(TRID) - gen_row_num_by_group(TRID) + 1 is it what you are saying wright?

kiran344 (BOB member since 2010-04-12)

#4

gen_row_num() doesn’t need a column as parameter, that wouldn’t make much sence since it’s just enummerating all rows.
gen_row_num_by_group(TRID) is correct though. And you need to sort by TRID in a query before that. But I guess you have figured that out already since you tested that function before your first post.

edit: I should read the examples more carefully. My “Formula” won’t give you what you want since the output would be

RID TRID GroupID 
5    2    1 
9    5    2 
10   5    2 
15   7    4 
45   9    5 
89   9    5 
97   9    5

If you want to get to your solution youll need to Split your input, do a distinct TRID and gen_row_num() as output in on one branch and join back to the other branch on TRID

HerdplattenToni (BOB member since 2011-05-13)

#5

Yes. Generating the sequence id for each group and then, joining back to the main data would be the only the solution, I could think.

:slight_smile:

Thanks,
VJ

ganesan.vijaykumar (BOB member since 2009-09-09)

#6

I would think of writing a Custom Function which uses the previous_row_value() function on a sorted source data (Sort Order : TRID, RID)

–> Assign a Counter with value 1
–> Whenever the TRID changes, increment the counter
–> If it don’t change by comparing TRID = previous_row_value(TRID) then don’t increment

ganeshxp :us: (BOB member since 2008-07-17)

#7

Hi ganesh,

can you please brief on achiving this using custom function.

kiran344 (BOB member since 2010-04-12)

#8

Hi Ganesh,

When I am trying to use previous_row_value() function on grouped and sorted data in Custom function, it says we cannot use this function. If you could guide me how I can access previous row value in custom function with or without using Previous_row_value() would be very helpful.

thanks in advance.

BOBJFan (BOB member since 2011-09-24)