BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: Upcoming Events: PGHBOUG: Nov 1.

How to generate same numbers for a group of data


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
kiran344
Principal Member
Principal Member



Joined: 12 Apr 2010

Posts: 197



PostPosted: Wed Jul 04, 2012 4:38 am 
Post subject: How to generate same numbers for a group of data

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
Back to top
HerdplattenToni
Principal Member
Principal Member



Joined: 13 May 2011

Posts: 161



PostPosted: Wed Jul 04, 2012 8:43 am 
Post subject: Re: How to generate same numbers for a group of data

gen_row_num() - gen_row_num_by_group() + 1 ?
Back to top
kiran344
Principal Member
Principal Member



Joined: 12 Apr 2010

Posts: 197



PostPosted: Wed Jul 04, 2012 8:52 am 
Post subject: Re: How to generate same numbers for a group of data

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?
Back to top
HerdplattenToni
Principal Member
Principal Member



Joined: 13 May 2011

Posts: 161



PostPosted: Thu Jul 05, 2012 2:00 am 
Post subject: Re: How to generate same numbers for a group of data

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
Code:
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
Back to top
ganesan.vijaykumar
Forum Member
Forum Member



Joined: 09 Sep 2009

Posts: 14



PostPosted: Fri Jul 06, 2012 9:17 am 
Post subject: Re: How to generate same numbers for a group of data

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.

icon_smile.gif

Thanks,
VJ
Back to top
ganeshxp
Forum Addict
Forum Addict



Joined: 17 Jul 2008
ASUG Icon
Posts: 3700
Location: Chicago, Illinois, USA


flag
PostPosted: Fri Jul 06, 2012 11:18 am 
Post subject: Re: How to generate same numbers for a group of data

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

_________________
Cheers
Ganesh
Back to top
kiran344
Principal Member
Principal Member



Joined: 12 Apr 2010

Posts: 197



PostPosted: Mon Jul 16, 2012 4:00 am 
Post subject: Re: How to generate same numbers for a group of data

Hi ganesh,


can you please brief on achiving this using custom function.
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Wed Aug 28, 2019 4:34 am 
Post subject: Re: How to generate same numbers for a group of data

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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0309 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0271)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo